<!DOCTYPE html>
<html lang="en"><head><script src="/livereload.js?mindelay=10&amp;v=2&amp;port=1313&amp;path=livereload" data-no-instant defer></script>
  <meta charset="utf-8" />
  <meta name="viewport" content="width=device-width, initial-scale=1.0" />
  <meta name="robots" content="noindex, nofollow" />
  <link rel="icon shortcut" href="/favicon.ico" sizes="32x32" />
<link rel="icon" href="/favicon.svg" type="image/svg+xml" />
<link rel="icon" href="/favicon-dark.svg" type="image/svg+xml" media="(prefers-color-scheme: dark)" />
<link rel="icon" href="/favicon-16x16.png" type="image/png" sizes="16x16" />
<link rel="icon" href="/favicon-32x32.png" type="image/png" sizes="32x32" />
<link rel="apple-touch-icon" href="/apple-touch-icon.png" sizes="180x180" />
<link fetchpriority="low" href="/site.webmanifest" rel="manifest" />

  <title>第 20 章：索引扫描 – PostgreSQL 14 Internals</title>
  <meta name="description" content="20.1 常规索引扫描 索引提供了两种基本的访问 TIDS 的方式。第一种是执行索引扫描。大多数索引访问方法 (但不是全部) 都具有 INDEX SCAN 属性以支持这种操作。
索引扫描在计划中由 Index Scan 1 节点表示：
=&gt; EXPLAIN SELECT * FROM bookings WHERE book_ref = &#39;9AC0C6&#39; AND total_amount = 48500.00; QUERY PLAN −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− Index Scan using bookings_pkey on bookings (cost=0.43..8.45 rows=1 width=21) Index Cond: (book_ref = &#39;9AC0C6&#39;::bpchar) Filter: (total_amount = 48500.00) (4 rows) 索引扫描期间，访问方法逐个返回 TIDS 2。接收到 TID 之后，索引引擎访问此 TID 引用的堆页面，获取相应的元组，如果满足可见性规则，则返回所请求的该元组相关字段。这个过程会持续进行，直到访问方法没有更多匹配查询的 TIDS 为止。" />

  
    <link rel="canonical" href="//localhost:1313/docs/chapter20/" itemprop="url" />
  

  

<meta property="og:title" content="第 20 章：索引扫描" />
<meta property="og:description" content="" />
<meta property="og:type" content="website" />
<meta property="og:url" content="//localhost:1313/docs/chapter20/" />

  
  <meta itemprop="name" content="第 20 章：索引扫描">
  <meta itemprop="description" content="20.1 常规索引扫描 索引提供了两种基本的访问 TIDS 的方式。第一种是执行索引扫描。大多数索引访问方法 (但不是全部) 都具有 INDEX SCAN 属性以支持这种操作。
索引扫描在计划中由 Index Scan 1 节点表示：
=&gt; EXPLAIN SELECT * FROM bookings WHERE book_ref = &#39;9AC0C6&#39; AND total_amount = 48500.00; QUERY PLAN −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− Index Scan using bookings_pkey on bookings (cost=0.43..8.45 rows=1 width=21) Index Cond: (book_ref = &#39;9AC0C6&#39;::bpchar) Filter: (total_amount = 48500.00) (4 rows) 索引扫描期间，访问方法逐个返回 TIDS 2。接收到 TID 之后，索引引擎访问此 TID 引用的堆页面，获取相应的元组，如果满足可见性规则，则返回所请求的该元组相关字段。这个过程会持续进行，直到访问方法没有更多匹配查询的 TIDS 为止。">
  <meta itemprop="wordCount" content="1925">
  <meta name="twitter:card" content="summary">
  <meta name="twitter:title" content="第 20 章：索引扫描">
  <meta name="twitter:description" content="20.1 常规索引扫描 索引提供了两种基本的访问 TIDS 的方式。第一种是执行索引扫描。大多数索引访问方法 (但不是全部) 都具有 INDEX SCAN 属性以支持这种操作。
索引扫描在计划中由 Index Scan 1 节点表示：
=&gt; EXPLAIN SELECT * FROM bookings WHERE book_ref = &#39;9AC0C6&#39; AND total_amount = 48500.00; QUERY PLAN −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− Index Scan using bookings_pkey on bookings (cost=0.43..8.45 rows=1 width=21) Index Cond: (book_ref = &#39;9AC0C6&#39;::bpchar) Filter: (total_amount = 48500.00) (4 rows) 索引扫描期间，访问方法逐个返回 TIDS 2。接收到 TID 之后，索引引擎访问此 TID 引用的堆页面，获取相应的元组，如果满足可见性规则，则返回所请求的该元组相关字段。这个过程会持续进行，直到访问方法没有更多匹配查询的 TIDS 为止。">

    <link href="/css/compiled/main.css" rel="stylesheet" />



  <link href="/css/custom.css" rel="stylesheet" />





  <script>
     
    const defaultTheme = 'light';

    const setDarkTheme = () => {
      document.documentElement.classList.add("dark");
      document.documentElement.style.colorScheme = "dark";
    }
    const setLightTheme = () => {
      document.documentElement.classList.remove("dark");
      document.documentElement.style.colorScheme = "light";
    }

    if ("color-theme" in localStorage) {
      localStorage.getItem("color-theme") === "dark" ? setDarkTheme() : setLightTheme();
    } else {
      defaultTheme === "dark" ? setDarkTheme() : setLightTheme();
      if (defaultTheme === "system") {
        window.matchMedia("(prefers-color-scheme: dark)").matches ? setDarkTheme() : setLightTheme();
      }
    }
  </script>

  
</head>
<body dir="ltr"><div class="nav-container hx-sticky hx-top-0 hx-z-20 hx-w-full hx-bg-transparent print:hx-hidden">
  <div class="nav-container-blur hx-pointer-events-none hx-absolute hx-z-[-1] hx-h-full hx-w-full hx-bg-white dark:hx-bg-dark hx-shadow-[0_2px_4px_rgba(0,0,0,.02),0_1px_0_rgba(0,0,0,.06)] contrast-more:hx-shadow-[0_0_0_1px_#000] dark:hx-shadow-[0_-1px_0_rgba(255,255,255,.1)_inset] contrast-more:dark:hx-shadow-[0_0_0_1px_#fff]"></div>

  <nav class="hx-mx-auto hx-flex hx-items-center hx-justify-end hx-gap-2 hx-h-16 hx-px-6 hx-max-w-[90rem]">
    <a class="hx-flex hx-items-center hover:hx-opacity-75 ltr:hx-mr-auto rtl:hx-ml-auto" href="/">
        <img class="hx-block dark:hx-hidden" src="/images/postgresql-elephant-in-potential.svg" alt="PostgreSQL 14 Internals" height="30" width="45" />
        <img class="hx-hidden dark:hx-block" src="/images/postgresql-elephant-in-power.svg" alt="PostgreSQL 14 Internals" height="30" width="45" />
        <span class="hx-mx-2 hx-font-extrabold hx-inline hx-select-none" title="PostgreSQL 14 Internals">PostgreSQL 14 Internals</span>
    </a><a
            title="Docs"
            href="/docs"
            
            class="hx-text-sm contrast-more:hx-text-gray-700 contrast-more:dark:hx-text-gray-100 hx-relative -hx-ml-2 hx-hidden hx-whitespace-nowrap hx-p-2 md:hx-inline-block hx-font-medium"
          >
            <span class="hx-text-center">Docs</span>
          </a><a
            title="About"
            href="/about"
            
            class="hx-text-sm contrast-more:hx-text-gray-700 contrast-more:dark:hx-text-gray-100 hx-relative -hx-ml-2 hx-hidden hx-whitespace-nowrap hx-p-2 md:hx-inline-block hx-text-gray-600 hover:hx-text-gray-800 dark:hx-text-gray-400 dark:hover:hx-text-gray-200"
          >
            <span class="hx-text-center">About</span>
          </a><a
            title="Contact ↗"
            href="https://github.com/xiongcccc"
            target="_blank" rel="noreferer"
            class="hx-text-sm contrast-more:hx-text-gray-700 contrast-more:dark:hx-text-gray-100 hx-relative -hx-ml-2 hx-hidden hx-whitespace-nowrap hx-p-2 md:hx-inline-block hx-text-gray-600 hover:hx-text-gray-800 dark:hx-text-gray-400 dark:hover:hx-text-gray-200"
          >
            <span class="hx-text-center">Contact ↗</span>
          </a><div class="search-wrapper hx-relative md:hx-w-64">
  <div class="hx-relative hx-flex hx-items-center hx-text-gray-900 contrast-more:hx-text-gray-800 dark:hx-text-gray-300 contrast-more:dark:hx-text-gray-300">
    <input
      placeholder="Search..."
      class="search-input hx-block hx-w-full hx-appearance-none hx-rounded-lg hx-px-3 hx-py-2 hx-transition-colors hx-text-base hx-leading-tight md:hx-text-sm hx-bg-black/[.05] dark:hx-bg-gray-50/10 focus:hx-bg-white dark:focus:hx-bg-dark placeholder:hx-text-gray-500 dark:placeholder:hx-text-gray-400 contrast-more:hx-border contrast-more:hx-border-current"
      type="search"
      value=""
      spellcheck="false"
    />
    <kbd
      class="hx-absolute hx-my-1.5 hx-select-none ltr:hx-right-1.5 rtl:hx-left-1.5 hx-h-5 hx-rounded hx-bg-white hx-px-1.5 hx-font-mono hx-text-[10px] hx-font-medium hx-text-gray-500 hx-border dark:hx-border-gray-100/20 dark:hx-bg-dark/50 contrast-more:hx-border-current contrast-more:hx-text-current contrast-more:dark:hx-border-current hx-items-center hx-gap-1 hx-transition-opacity hx-pointer-events-none hx-hidden sm:hx-flex"
    >
      CTRL K
    </kbd>
  </div>

  <div>
    <ul
      class="search-results hextra-scrollbar hx-hidden hx-border hx-border-gray-200 hx-bg-white hx-text-gray-100 dark:hx-border-neutral-800 dark:hx-bg-neutral-900 hx-absolute hx-top-full hx-z-20 hx-mt-2 hx-overflow-auto hx-overscroll-contain hx-rounded-xl hx-py-2.5 hx-shadow-xl hx-max-h-[min(calc(50vh-11rem-env(safe-area-inset-bottom)),400px)] md:hx-max-h-[min(calc(100vh-5rem-env(safe-area-inset-bottom)),400px)] hx-inset-x-0 ltr:md:hx-left-auto rtl:md:hx-right-auto contrast-more:hx-border contrast-more:hx-border-gray-900 contrast-more:dark:hx-border-gray-50 hx-w-screen hx-min-h-[100px] hx-max-w-[min(calc(100vw-2rem),calc(100%+20rem))]"
      style="transition: max-height 0.2s ease 0s;"
    ></ul>
  </div>
</div>

          <a class="hx-p-2 hx-text-current" target="_blank" rel="noreferer" href="https://github.com/xiongcccc/xiongcccc.github.io" title="GitHub"><svg height=24 fill="currentColor" viewBox="3 3 18 18">
  <path d="M12 3C7.0275 3 3 7.12937 3 12.2276C3 16.3109 5.57625 19.7597 9.15374 20.9824C9.60374 21.0631 9.77249 20.7863 9.77249 20.5441C9.77249 20.3249 9.76125 19.5982 9.76125 18.8254C7.5 19.2522 6.915 18.2602 6.735 17.7412C6.63375 17.4759 6.19499 16.6569 5.8125 16.4378C5.4975 16.2647 5.0475 15.838 5.80124 15.8264C6.51 15.8149 7.01625 16.4954 7.18499 16.7723C7.99499 18.1679 9.28875 17.7758 9.80625 17.5335C9.885 16.9337 10.1212 16.53 10.38 16.2993C8.3775 16.0687 6.285 15.2728 6.285 11.7432C6.285 10.7397 6.63375 9.9092 7.20749 9.26326C7.1175 9.03257 6.8025 8.08674 7.2975 6.81794C7.2975 6.81794 8.05125 6.57571 9.77249 7.76377C10.4925 7.55615 11.2575 7.45234 12.0225 7.45234C12.7875 7.45234 13.5525 7.55615 14.2725 7.76377C15.9937 6.56418 16.7475 6.81794 16.7475 6.81794C17.2424 8.08674 16.9275 9.03257 16.8375 9.26326C17.4113 9.9092 17.76 10.7281 17.76 11.7432C17.76 15.2843 15.6563 16.0687 13.6537 16.2993C13.98 16.5877 14.2613 17.1414 14.2613 18.0065C14.2613 19.2407 14.25 20.2326 14.25 20.5441C14.25 20.7863 14.4188 21.0746 14.8688 20.9824C16.6554 20.364 18.2079 19.1866 19.3078 17.6162C20.4077 16.0457 20.9995 14.1611 21 12.2276C21 7.12937 16.9725 3 12 3Z"></path>
</svg>
<span class="hx-sr-only">GitHub</span>
          </a>
          <a class="hx-p-2 hx-text-current" target="_blank" rel="noreferer" href="https://twitter.com/" title="Twitter"><svg height=24 xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path fill="currentColor" d="M389.2 48h70.6L305.6 224.2 487 464H345L233.7 318.6 106.5 464H35.8L200.7 275.5 26.8 48H172.4L272.9 180.9 389.2 48zM364.4 421.8h39.1L151.1 88h-42L364.4 421.8z"/></svg><span class="hx-sr-only">Twitter</span>
          </a><button type="button" aria-label="Menu" class="hamburger-menu -hx-mr-2 hx-rounded hx-p-2 active:hx-bg-gray-400/20 md:hx-hidden"><svg height=24 fill="none" viewBox="0 0 24 24" stroke="currentColor"><g><path stroke-linecap="round" stroke-linejoin="round" stroke-width="2" d="M4 8H20"></path></g><g><path stroke-linecap="round" stroke-linejoin="round" stroke-width="2" d="M4 16H20"></path></g></svg></button>
  </nav>
</div>

  <div class='hx-mx-auto hx-flex hx-max-w-[90rem]'>
    <div class="mobile-menu-overlay [transition:background-color_1.5s_ease] hx-fixed hx-inset-0 hx-z-10 hx-bg-black/80 dark:hx-bg-black/60 hx-hidden"></div>
<aside class="sidebar-container hx-flex hx-flex-col print:hx-hidden md:hx-top-16 md:hx-shrink-0 md:hx-w-64 md:hx-self-start max-md:[transform:translate3d(0,-100%,0)] md:hx-sticky">
  
  <div class="hx-px-4 hx-pt-4 md:hx-hidden">
    <div class="search-wrapper hx-relative md:hx-w-64">
  <div class="hx-relative hx-flex hx-items-center hx-text-gray-900 contrast-more:hx-text-gray-800 dark:hx-text-gray-300 contrast-more:dark:hx-text-gray-300">
    <input
      placeholder="Search..."
      class="search-input hx-block hx-w-full hx-appearance-none hx-rounded-lg hx-px-3 hx-py-2 hx-transition-colors hx-text-base hx-leading-tight md:hx-text-sm hx-bg-black/[.05] dark:hx-bg-gray-50/10 focus:hx-bg-white dark:focus:hx-bg-dark placeholder:hx-text-gray-500 dark:placeholder:hx-text-gray-400 contrast-more:hx-border contrast-more:hx-border-current"
      type="search"
      value=""
      spellcheck="false"
    />
    <kbd
      class="hx-absolute hx-my-1.5 hx-select-none ltr:hx-right-1.5 rtl:hx-left-1.5 hx-h-5 hx-rounded hx-bg-white hx-px-1.5 hx-font-mono hx-text-[10px] hx-font-medium hx-text-gray-500 hx-border dark:hx-border-gray-100/20 dark:hx-bg-dark/50 contrast-more:hx-border-current contrast-more:hx-text-current contrast-more:dark:hx-border-current hx-items-center hx-gap-1 hx-transition-opacity hx-pointer-events-none hx-hidden sm:hx-flex"
    >
      CTRL K
    </kbd>
  </div>

  <div>
    <ul
      class="search-results hextra-scrollbar hx-hidden hx-border hx-border-gray-200 hx-bg-white hx-text-gray-100 dark:hx-border-neutral-800 dark:hx-bg-neutral-900 hx-absolute hx-top-full hx-z-20 hx-mt-2 hx-overflow-auto hx-overscroll-contain hx-rounded-xl hx-py-2.5 hx-shadow-xl hx-max-h-[min(calc(50vh-11rem-env(safe-area-inset-bottom)),400px)] md:hx-max-h-[min(calc(100vh-5rem-env(safe-area-inset-bottom)),400px)] hx-inset-x-0 ltr:md:hx-left-auto rtl:md:hx-right-auto contrast-more:hx-border contrast-more:hx-border-gray-900 contrast-more:dark:hx-border-gray-50 hx-w-screen hx-min-h-[100px] hx-max-w-[min(calc(100vw-2rem),calc(100%+20rem))]"
      style="transition: max-height 0.2s ease 0s;"
    ></ul>
  </div>
</div>

  </div>
  <div class="hextra-scrollbar hx-overflow-y-auto hx-overflow-x-hidden hx-p-4 hx-grow md:hx-h-[calc(100vh-var(--navbar-height)-var(--menu-height))]">
    <ul class="hx-flex hx-flex-col hx-gap-1 md:hx-hidden">
      
      
          <li class=""><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/about/"
    
  >About
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/"
    
  >Docs
        <span class="hextra-sidebar-collapsible-button"><svg fill="none" viewBox="0 0 24 24" stroke="currentColor" class="hx-h-[18px] hx-min-w-[18px] hx-rounded-sm hx-p-0.5 hover:hx-bg-gray-800/5 dark:hover:hx-bg-gray-100/5"><path stroke-linecap="round" stroke-linejoin="round" stroke-width="2" d="M9 5l7 7-7 7" class="hx-origin-center hx-transition-transform rtl:-hx-rotate-180"></path></svg></span>
    </a><div class="ltr:hx-pr-0 hx-overflow-hidden">
        <ul class='hx-relative hx-flex hx-flex-col hx-gap-1 before:hx-absolute before:hx-inset-y-1 before:hx-w-px before:hx-bg-gray-200 before:hx-content-[""] ltr:hx-ml-3 ltr:hx-pl-3 ltr:before:hx-left-0 rtl:hx-mr-3 rtl:hx-pr-3 rtl:before:hx-right-0 dark:before:hx-bg-neutral-800'><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter01/"
    
  >第 1 章：介绍
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter02/"
    
  >第 2 章：隔离性
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter03/"
    
  >第 3 章：页与元组
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter04/"
    
  >第 4 章：快照
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter05/"
    
  >第 5 章：页剪枝与 HOT 更新
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter06/"
    
  >第 6 章：Vacuum 与 Autovacuum
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter07/"
    
  >第 7 章：冻结
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter08/"
    
  >第 8 章：重建表与索引
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter09/"
    
  >第 9 章：缓冲区缓存
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter10/"
    
  >第 10 章：预写式日志
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter11/"
    
  >第 11 章：WAL 模式
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter12/"
    
  >第 12 章：关系级锁
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter13/"
    
  >第 13 章：行级锁
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter14/"
    
  >第 14 章：多样的锁
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter15/"
    
  >第 15 章：内存结构上的锁
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter16/"
    
  >第 16 章：查询执行阶段
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter17/"
    
  >第 17 章：统计信息
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter18/"
    
  >第 18 章：表访问方法
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter19/"
    
  >第 19 章：索引访问方法
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      sidebar-active-item hx-bg-primary-100 hx-font-semibold hx-text-primary-800 contrast-more:hx-border contrast-more:hx-border-primary-500 dark:hx-bg-primary-400/10 dark:hx-text-primary-600 contrast-more:dark:hx-border-primary-500"
    href="/docs/chapter20/"
    
  >第 20 章：索引扫描
    </a>
  
    <ul class='hx-flex hx-flex-col hx-gap-1 hx-relative before:hx-absolute before:hx-inset-y-1 before:hx-w-px before:hx-bg-gray-200 before:hx-content-[""] dark:before:hx-bg-neutral-800 ltr:hx-pl-3 ltr:before:hx-left-0 rtl:hx-pr-3 rtl:before:hx-right-0 ltr:hx-ml-3 rtl:hx-mr-3'><li>
              <a
                href="#201-%e5%b8%b8%e8%a7%84%e7%b4%a2%e5%bc%95%e6%89%ab%e6%8f%8f"
                class="hx-flex hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [word-break:break-word] hx-cursor-pointer [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] contrast-more:hx-border hx-gap-2 before:hx-opacity-25 before:hx-content-['#'] hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:hx-text-gray-900 contrast-more:dark:hx-text-gray-50 contrast-more:hx-border-transparent contrast-more:hover:hx-border-gray-900 contrast-more:dark:hover:hx-border-gray-50"
              >20.1 常规索引扫描</a>
            </li>
          <li>
              <a
                href="#202-%e4%bb%85%e7%b4%a2%e5%bc%95%e6%89%ab%e6%8f%8f"
                class="hx-flex hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [word-break:break-word] hx-cursor-pointer [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] contrast-more:hx-border hx-gap-2 before:hx-opacity-25 before:hx-content-['#'] hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:hx-text-gray-900 contrast-more:dark:hx-text-gray-50 contrast-more:hx-border-transparent contrast-more:hover:hx-border-gray-900 contrast-more:dark:hover:hx-border-gray-50"
              >20.2 仅索引扫描</a>
            </li>
          <li>
              <a
                href="#203-%e4%bd%8d%e5%9b%be%e6%89%ab%e6%8f%8f"
                class="hx-flex hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [word-break:break-word] hx-cursor-pointer [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] contrast-more:hx-border hx-gap-2 before:hx-opacity-25 before:hx-content-['#'] hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:hx-text-gray-900 contrast-more:dark:hx-text-gray-50 contrast-more:hx-border-transparent contrast-more:hover:hx-border-gray-900 contrast-more:dark:hover:hx-border-gray-50"
              >20.3 位图扫描</a>
            </li>
          <li>
              <a
                href="#204-%e5%b9%b6%e8%a1%8c%e7%b4%a2%e5%bc%95%e6%89%ab%e6%8f%8f"
                class="hx-flex hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [word-break:break-word] hx-cursor-pointer [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] contrast-more:hx-border hx-gap-2 before:hx-opacity-25 before:hx-content-['#'] hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:hx-text-gray-900 contrast-more:dark:hx-text-gray-50 contrast-more:hx-border-transparent contrast-more:hover:hx-border-gray-900 contrast-more:dark:hover:hx-border-gray-50"
              >20.4 并行索引扫描</a>
            </li>
          <li>
              <a
                href="#205-%e5%90%84%e7%a7%8d%e8%ae%bf%e9%97%ae%e6%96%b9%e6%b3%95%e7%9a%84%e6%af%94%e8%be%83"
                class="hx-flex hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [word-break:break-word] hx-cursor-pointer [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] contrast-more:hx-border hx-gap-2 before:hx-opacity-25 before:hx-content-['#'] hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:hx-text-gray-900 contrast-more:dark:hx-text-gray-50 contrast-more:hx-border-transparent contrast-more:hover:hx-border-gray-900 contrast-more:dark:hover:hx-border-gray-50"
              >20.5 各种访问方法的比较</a>
            </li>
          </ul>
  
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter21/"
    
  >第 21 章：嵌套循环
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter22/"
    
  >第 22 章：哈希
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter23/"
    
  >第 23 章：排序与归并
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter24/"
    
  >第 24 章：Hash
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter25/"
    
  >第 25 章：B-tree
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter26/"
    
  >第 26 章：GiST
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter27/"
    
  >第 27 章：SP-GiST
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter28/"
    
  >第 28 章：GIN
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter29/"
    
  >第 29 章：BRIN
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter30/"
    
  >结论
    </a>
              
            </li></ul>
      </div></li>
          <li class=""><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/recommend/"
    
  >Recommend
    </a></li>
    </ul>

    <ul class="hx-flex hx-flex-col hx-gap-1 max-md:hx-hidden">
        
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter01/"
    
  >第 1 章：介绍
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter02/"
    
  >第 2 章：隔离性
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter03/"
    
  >第 3 章：页与元组
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter04/"
    
  >第 4 章：快照
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter05/"
    
  >第 5 章：页剪枝与 HOT 更新
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter06/"
    
  >第 6 章：Vacuum 与 Autovacuum
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter07/"
    
  >第 7 章：冻结
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter08/"
    
  >第 8 章：重建表与索引
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter09/"
    
  >第 9 章：缓冲区缓存
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter10/"
    
  >第 10 章：预写式日志
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter11/"
    
  >第 11 章：WAL 模式
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter12/"
    
  >第 12 章：关系级锁
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter13/"
    
  >第 13 章：行级锁
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter14/"
    
  >第 14 章：多样的锁
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter15/"
    
  >第 15 章：内存结构上的锁
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter16/"
    
  >第 16 章：查询执行阶段
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter17/"
    
  >第 17 章：统计信息
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter18/"
    
  >第 18 章：表访问方法
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter19/"
    
  >第 19 章：索引访问方法
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      sidebar-active-item hx-bg-primary-100 hx-font-semibold hx-text-primary-800 contrast-more:hx-border contrast-more:hx-border-primary-500 dark:hx-bg-primary-400/10 dark:hx-text-primary-600 contrast-more:dark:hx-border-primary-500"
    href="/docs/chapter20/"
    
  >第 20 章：索引扫描
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter21/"
    
  >第 21 章：嵌套循环
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter22/"
    
  >第 22 章：哈希
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter23/"
    
  >第 23 章：排序与归并
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter24/"
    
  >第 24 章：Hash
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter25/"
    
  >第 25 章：B-tree
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter26/"
    
  >第 26 章：GiST
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter27/"
    
  >第 27 章：SP-GiST
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter28/"
    
  >第 28 章：GIN
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter29/"
    
  >第 29 章：BRIN
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter30/"
    
  >结论
    </a></li>
        
      </ul>
    </div>
  
  
    <div class="  hx-sticky hx-bottom-0 hx-bg-white dark:hx-bg-dark hx-mx-4 hx-py-4 hx-shadow-[0_-12px_16px_#fff] hx-flex hx-items-center hx-gap-2 dark:hx-border-neutral-800 dark:hx-shadow-[0_-12px_16px_#111] contrast-more:hx-border-neutral-400 contrast-more:hx-shadow-none contrast-more:dark:hx-shadow-none hx-border-t" data-toggle-animation="show"><div class="hx-flex hx-grow hx-flex-col"><button
  title="Change theme"
  data-theme="light"
  class="theme-toggle hx-group hx-h-7 hx-rounded-md hx-px-2 hx-text-left hx-text-xs hx-font-medium hx-text-gray-600 hx-transition-colors dark:hx-text-gray-400 hover:hx-bg-gray-100 hover:hx-text-gray-900 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50"
  type="button"
  aria-label="Change theme"
>
  <div class="hx-flex hx-items-center hx-gap-2 hx-capitalize"><svg height=12 class="group-data-[theme=light]:hx-hidden" xmlns="http://www.w3.org/2000/svg" fill="none" viewBox="0 0 24 24" stroke-width="2" stroke="currentColor" aria-hidden="true"><path stroke-linecap="round" stroke-linejoin="round" d="M12 3v1m0 16v1m9-9h-1M4 12H3m15.364 6.364l-.707-.707M6.343 6.343l-.707-.707m12.728 0l-.707.707M6.343 17.657l-.707.707M16 12a4 4 0 11-8 0 4 4 0 018 0z"/></svg><span class="group-data-[theme=light]:hx-hidden">Light</span><svg height=12 class="group-data-[theme=dark]:hx-hidden" xmlns="http://www.w3.org/2000/svg" fill="none" viewBox="0 0 24 24" stroke-width="2" stroke="currentColor" aria-hidden="true"><path stroke-linecap="round" stroke-linejoin="round" d="M20.354 15.354A9 9 0 018.646 3.646 9.003 9.003 0 0012 21a9.003 9.003 0 008.354-5.646z"/></svg><span class="group-data-[theme=dark]:hx-hidden">Dark</span></div>
</button>
</div></div></aside>
    
<nav class="hextra-toc hx-order-last hx-hidden hx-w-64 hx-shrink-0 xl:hx-block print:hx-hidden hx-px-4" aria-label="table of contents">
    <div class="hextra-scrollbar hx-sticky hx-top-16 hx-overflow-y-auto hx-pr-4 hx-pt-6 hx-text-sm [hyphens:auto] hx-max-h-[calc(100vh-var(--navbar-height)-env(safe-area-inset-bottom))] ltr:hx--mr-4 rtl:hx--ml-4"><p class="hx-mb-4 hx-font-semibold hx-tracking-tight">On this page</p><ul>
      <li class="hx-my-2 hx-scroll-my-6 hx-scroll-py-6">
        <a class="hx-font-semibold hx-inline-block hx-text-gray-500 hover:hx-text-gray-900 dark:hx-text-gray-400 dark:hover:hx-text-gray-300 contrast-more:hx-text-gray-900 contrast-more:hx-underline contrast-more:dark:hx-text-gray-50 hx-w-full hx-break-words" href="#201-%e5%b8%b8%e8%a7%84%e7%b4%a2%e5%bc%95%e6%89%ab%e6%8f%8f">20.1 常规索引扫描
        </a>
      </li>
      <li class="hx-my-2 hx-scroll-my-6 hx-scroll-py-6">
        <a class="ltr:hx-pl-4 rtl:hx-pr-4 hx-inline-block hx-text-gray-500 hover:hx-text-gray-900 dark:hx-text-gray-400 dark:hover:hx-text-gray-300 contrast-more:hx-text-gray-900 contrast-more:hx-underline contrast-more:dark:hx-text-gray-50 hx-w-full hx-break-words" href="#2011-%e6%88%90%e6%9c%ac%e4%bc%b0%e7%ae%97">20.1.1 成本估算
        </a>
      </li>
      <li class="hx-my-2 hx-scroll-my-6 hx-scroll-py-6">
        <a class="ltr:hx-pl-4 rtl:hx-pr-4 hx-inline-block hx-text-gray-500 hover:hx-text-gray-900 dark:hx-text-gray-400 dark:hover:hx-text-gray-300 contrast-more:hx-text-gray-900 contrast-more:hx-underline contrast-more:dark:hx-text-gray-50 hx-w-full hx-break-words" href="#2012-%e8%89%af%e5%a5%bd%e5%9c%ba%e6%99%af%e9%ab%98%e7%9b%b8%e5%85%b3%e6%80%a7">20.1.2 良好场景：高相关性
        </a>
      </li>
      <li class="hx-my-2 hx-scroll-my-6 hx-scroll-py-6">
        <a class="ltr:hx-pl-4 rtl:hx-pr-4 hx-inline-block hx-text-gray-500 hover:hx-text-gray-900 dark:hx-text-gray-400 dark:hover:hx-text-gray-300 contrast-more:hx-text-gray-900 contrast-more:hx-underline contrast-more:dark:hx-text-gray-50 hx-w-full hx-break-words" href="#2013-%e4%b8%8d%e8%89%af%e5%9c%ba%e6%99%af%e4%bd%8e%e7%9b%b8%e5%85%b3%e6%80%a7">20.1.3 不良场景：低相关性
        </a>
      </li>
      <li class="hx-my-2 hx-scroll-my-6 hx-scroll-py-6">
        <a class="hx-font-semibold hx-inline-block hx-text-gray-500 hover:hx-text-gray-900 dark:hx-text-gray-400 dark:hover:hx-text-gray-300 contrast-more:hx-text-gray-900 contrast-more:hx-underline contrast-more:dark:hx-text-gray-50 hx-w-full hx-break-words" href="#202-%e4%bb%85%e7%b4%a2%e5%bc%95%e6%89%ab%e6%8f%8f">20.2 仅索引扫描
        </a>
      </li>
      <li class="hx-my-2 hx-scroll-my-6 hx-scroll-py-6">
        <a class="ltr:hx-pl-4 rtl:hx-pr-4 hx-inline-block hx-text-gray-500 hover:hx-text-gray-900 dark:hx-text-gray-400 dark:hover:hx-text-gray-300 contrast-more:hx-text-gray-900 contrast-more:hx-underline contrast-more:dark:hx-text-gray-50 hx-w-full hx-break-words" href="#2021-%e5%8c%85%e5%90%ab-include-%e5%ad%90%e5%8f%a5%e7%9a%84%e7%b4%a2%e5%bc%95">20.2.1 包含 INCLUDE 子句的索引
        </a>
      </li>
      <li class="hx-my-2 hx-scroll-my-6 hx-scroll-py-6">
        <a class="hx-font-semibold hx-inline-block hx-text-gray-500 hover:hx-text-gray-900 dark:hx-text-gray-400 dark:hover:hx-text-gray-300 contrast-more:hx-text-gray-900 contrast-more:hx-underline contrast-more:dark:hx-text-gray-50 hx-w-full hx-break-words" href="#203-%e4%bd%8d%e5%9b%be%e6%89%ab%e6%8f%8f">20.3 位图扫描
        </a>
      </li>
      <li class="hx-my-2 hx-scroll-my-6 hx-scroll-py-6">
        <a class="ltr:hx-pl-4 rtl:hx-pr-4 hx-inline-block hx-text-gray-500 hover:hx-text-gray-900 dark:hx-text-gray-400 dark:hover:hx-text-gray-300 contrast-more:hx-text-gray-900 contrast-more:hx-underline contrast-more:dark:hx-text-gray-50 hx-w-full hx-break-words" href="#2031-%e4%bd%8d%e5%9b%be%e5%87%86%e7%a1%ae%e6%80%a7">20.3.1 位图准确性
        </a>
      </li>
      <li class="hx-my-2 hx-scroll-my-6 hx-scroll-py-6">
        <a class="ltr:hx-pl-4 rtl:hx-pr-4 hx-inline-block hx-text-gray-500 hover:hx-text-gray-900 dark:hx-text-gray-400 dark:hover:hx-text-gray-300 contrast-more:hx-text-gray-900 contrast-more:hx-underline contrast-more:dark:hx-text-gray-50 hx-w-full hx-break-words" href="#2032-%e4%bd%8d%e5%9b%be%e6%93%8d%e4%bd%9c">20.3.2 位图操作
        </a>
      </li>
      <li class="hx-my-2 hx-scroll-my-6 hx-scroll-py-6">
        <a class="ltr:hx-pl-4 rtl:hx-pr-4 hx-inline-block hx-text-gray-500 hover:hx-text-gray-900 dark:hx-text-gray-400 dark:hover:hx-text-gray-300 contrast-more:hx-text-gray-900 contrast-more:hx-underline contrast-more:dark:hx-text-gray-50 hx-w-full hx-break-words" href="#2033-%e6%88%90%e6%9c%ac%e4%bc%b0%e7%ae%97">20.3.3 成本估算
        </a>
      </li>
      <li class="hx-my-2 hx-scroll-my-6 hx-scroll-py-6">
        <a class="hx-font-semibold hx-inline-block hx-text-gray-500 hover:hx-text-gray-900 dark:hx-text-gray-400 dark:hover:hx-text-gray-300 contrast-more:hx-text-gray-900 contrast-more:hx-underline contrast-more:dark:hx-text-gray-50 hx-w-full hx-break-words" href="#204-%e5%b9%b6%e8%a1%8c%e7%b4%a2%e5%bc%95%e6%89%ab%e6%8f%8f">20.4 并行索引扫描
        </a>
      </li>
      <li class="hx-my-2 hx-scroll-my-6 hx-scroll-py-6">
        <a class="hx-font-semibold hx-inline-block hx-text-gray-500 hover:hx-text-gray-900 dark:hx-text-gray-400 dark:hover:hx-text-gray-300 contrast-more:hx-text-gray-900 contrast-more:hx-underline contrast-more:dark:hx-text-gray-50 hx-w-full hx-break-words" href="#205-%e5%90%84%e7%a7%8d%e8%ae%bf%e9%97%ae%e6%96%b9%e6%b3%95%e7%9a%84%e6%af%94%e8%be%83">20.5 各种访问方法的比较
        </a>
      </li></ul>
      <div class="hx-mt-8 hx-border-t hx-bg-white hx-pt-8 hx-shadow-[0_-12px_16px_white] dark:hx-bg-dark dark:hx-shadow-[0_-12px_16px_#111] hx-sticky hx-bottom-0 hx-flex hx-flex-col hx-items-start hx-gap-2 hx-pb-8 dark:hx-border-neutral-800 contrast-more:hx-border-t contrast-more:hx-border-neutral-400 contrast-more:hx-shadow-none contrast-more:dark:hx-border-neutral-400">
        <button aria-hidden="true" id="backToTop" onClick="scrollUp();" class="hx-transition-all hx-duration-75 hx-opacity-0 hx-text-xs hx-font-medium hx-text-gray-500 hover:hx-text-gray-900 dark:hx-text-gray-400 dark:hover:hx-text-gray-100 contrast-more:hx-text-gray-800 contrast-more:dark:hx-text-gray-50">
          <span>Scroll to top</span>
          <svg xmlns="http://www.w3.org/2000/svg" fill="none" viewBox="0 0 24 24" stroke-width="1.5" stroke="currentColor" class="hx-inline ltr:hx-ml-1 rtl:hx-mr-1 hx-h-3.5 hx-w-3.5 hx-border hx-rounded-full hx-border-gray-500 hover:hx-border-gray-900 dark:hx-border-gray-400 dark:hover:hx-border-gray-100 contrast-more:hx-border-gray-800 contrast-more:dark:hx-border-gray-50">
            <path stroke-linecap="round" stroke-linejoin="round" d="M4.5 15.75l7.5-7.5 7.5 7.5" />
          </svg>
        </button>
      </div>
    </div>
  </nav>


    <article class="hx-w-full hx-break-words hx-flex hx-min-h-[calc(100vh-var(--navbar-height))] hx-min-w-0 hx-justify-center hx-pb-8 hx-pr-[calc(env(safe-area-inset-right)-1.5rem)]">
      <main class="hx-w-full hx-min-w-0 hx-max-w-6xl hx-px-6 hx-pt-4 md:hx-px-12">
        
  <div class="hx-mt-1.5 hx-flex hx-items-center hx-gap-1 hx-overflow-hidden hx-text-sm hx-text-gray-500 dark:hx-text-gray-400 contrast-more:hx-text-current">
        <div class="hx-whitespace-nowrap hx-transition-colors hx-min-w-[24px] hx-overflow-hidden hx-text-ellipsis hover:hx-text-gray-900 dark:hover:hx-text-gray-100">
          <a href="/docs/">Docs</a>
        </div><svg class="hx-w-3.5 hx-shrink-0 rtl:-hx-rotate-180" xmlns="http://www.w3.org/2000/svg" fill="none" viewBox="0 0 24 24" stroke-width="2" stroke="currentColor" aria-hidden="true"><path stroke-linecap="round" stroke-linejoin="round" d="M9 5l7 7-7 7"/></svg><div class="hx-whitespace-nowrap hx-transition-colors hx-font-medium hx-text-gray-700 contrast-more:hx-font-bold contrast-more:hx-text-current dark:hx-text-gray-100 contrast-more:dark:hx-text-current">第 20 章：索引扫描</div>
  </div>

        <div class="content">
          <h1>第 20 章：索引扫描</h1>
          <h2>20.1 常规索引扫描<span class="hx-absolute -hx-mt-20" id="201-常规索引扫描"></span>
    <a href="#201-%e5%b8%b8%e8%a7%84%e7%b4%a2%e5%bc%95%e6%89%ab%e6%8f%8f" class="subheading-anchor" aria-label="Permalink for this section"></a></h2><p>索引提供了两种基本的访问 TIDS 的方式。第一种是执行索引扫描。大多数索引访问方法 (但不是全部) 都具有 INDEX SCAN 属性以支持这种操作。</p>
<p>索引扫描在计划中由 Index Scan <sup id="fnref:1"><a href="#fn:1" class="footnote-ref" role="doc-noteref">1</a></sup>  节点表示：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; EXPLAIN SELECT * FROM bookings
</span></span><span class="line"><span class="cl">WHERE <span class="nv">book_ref</span> <span class="o">=</span> <span class="s1">&#39;9AC0C6&#39;</span> AND <span class="nv">total_amount</span> <span class="o">=</span> 48500.00<span class="p">;</span>
</span></span><span class="line"><span class="cl">                 QUERY PLAN
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> Index Scan using bookings_pkey on bookings
</span></span><span class="line"><span class="cl">   <span class="o">(</span><span class="nv">cost</span><span class="o">=</span>0.43..8.45 <span class="nv">rows</span><span class="o">=</span><span class="m">1</span> <span class="nv">width</span><span class="o">=</span>21<span class="o">)</span>
</span></span><span class="line"><span class="cl">   Index Cond: <span class="o">(</span><span class="nv">book_ref</span> <span class="o">=</span> <span class="s1">&#39;9AC0C6&#39;</span>::bpchar<span class="o">)</span>
</span></span><span class="line"><span class="cl">   Filter: <span class="o">(</span><span class="nv">total_amount</span> <span class="o">=</span> 48500.00<span class="o">)</span>
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">4</span> rows<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>索引扫描期间，访问方法逐个返回 TIDS <sup id="fnref:2"><a href="#fn:2" class="footnote-ref" role="doc-noteref">2</a></sup>。接收到 TID 之后，索引引擎访问此 TID 引用的堆页面，获取相应的元组，如果满足可见性规则，则返回所请求的该元组相关字段。这个过程会持续进行，直到访问方法没有更多匹配查询的 TIDS 为止。</p>
<p>Index Cond 行仅包括可以使用索引检查的过滤条件。其他需要根据堆重新检查的条件将在 Filter 行中单独列出。</p>
<p>如本例所示，索引和堆访问操作均由一个共同的 Index Scan 节点处理，而不是由两个不同的节点处理。但是，还有一个单独的 Tid Scan 节点 <sup id="fnref:3"><a href="#fn:3" class="footnote-ref" role="doc-noteref">3</a></sup>，如果提前知道元组 ID，便可从堆中获取元组：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; EXPLAIN SELECT * FROM bookings WHERE <span class="nv">ctid</span> <span class="o">=</span> <span class="s1">&#39;(0,1)&#39;</span>::tid<span class="p">;</span>
</span></span><span class="line"><span class="cl">                       QUERY PLAN
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> Tid Scan on bookings <span class="o">(</span><span class="nv">cost</span><span class="o">=</span>0.00..4.01 <span class="nv">rows</span><span class="o">=</span><span class="m">1</span> <span class="nv">width</span><span class="o">=</span>21<span class="o">)</span>
</span></span><span class="line"><span class="cl">   TID Cond: <span class="o">(</span><span class="nv">ctid</span> <span class="o">=</span> <span class="s1">&#39;(0,1)&#39;</span>::tid<span class="o">)</span>
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">2</span> rows<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<h3>20.1.1 成本估算<span class="hx-absolute -hx-mt-20" id="2011-成本估算"></span>
    <a href="#2011-%e6%88%90%e6%9c%ac%e4%bc%b0%e7%ae%97" class="subheading-anchor" aria-label="Permalink for this section"></a></h3><p>索引扫描的成本估算包括索引访问操作和堆页面读取的预估成本。</p>
<p>显然，索引相关的估算部分完全取决于特定的访问方法。对于 B 树，成本主要由获取索引页和处理其条目所产生。要读取的页数和行数可以通过数据总量和应用的过滤条件的选择率来确定。索引页是随机访问的 (在逻辑结构上相邻的页面在磁盘上是物理分散的) 。从根节点到叶节点并计算所有需要的表达式所花费的 CPU 资源进一步增加了预估成本。<sup id="fnref:4"><a href="#fn:4" class="footnote-ref" role="doc-noteref">4</a></sup></p>
<p>堆相关部分的估算包括堆页面访问的成本和处理所有获取的元组所需的 CPU 时间。需要注意的是，I/O 估算取决于索引扫描的选择率以及磁盘上元组的物理顺序与访问方法返回其 ID 的顺序之间的相关性。</p>
<h3>20.1.2 良好场景：高相关性<span class="hx-absolute -hx-mt-20" id="2012-良好场景高相关性"></span>
    <a href="#2012-%e8%89%af%e5%a5%bd%e5%9c%ba%e6%99%af%e9%ab%98%e7%9b%b8%e5%85%b3%e6%80%a7" class="subheading-anchor" aria-label="Permalink for this section"></a></h3><p>如果元组在磁盘上的物理顺序与索引中 TIDS 的逻辑顺序完美相关，那么每个页面只会被访问一次：Index Scan 节点将顺序地从一个页面跳到另一个页面，逐个读取元组。</p>
<img src="20-1.png" style="width:80%; float:left" />
<div style="clear:both;"></div>
<p>PostgreSQL 会收集相关性的统计信息：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; SELECT attname, correlation
</span></span><span class="line"><span class="cl">FROM pg_stats WHERE <span class="nv">tablename</span> <span class="o">=</span> <span class="s1">&#39;bookings&#39;</span>
</span></span><span class="line"><span class="cl">ORDER BY abs<span class="o">(</span>correlation<span class="o">)</span> DESC<span class="p">;</span>
</span></span><span class="line"><span class="cl">   attname    <span class="p">|</span> correlation
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−+−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> book_ref     <span class="p">|</span>            <span class="m">1</span>
</span></span><span class="line"><span class="cl"> total_amount <span class="p">|</span> 0.0026738467
</span></span><span class="line"><span class="cl"> book_date    <span class="p">|</span>  8.02188e−05
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">3</span> rows<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>如果相应的绝对值接近于 1，那么相关性很高 (如 book_ref 的情况)；接近于零的值表示数据分布是混乱的。</p>
<blockquote>
<p>在这个特定例子中，book_ref 列的高相关性当然是因为数据是基于该列升序加载到表中的，并且还没有更新。如果我们对这一列上创建的索引执行 CLUSTER 命令，我们也会看到相同的结果。</p>
<p>但是，完美的相关性并不能保证所有的查询都将按照 book_ref 值的升序返回结果。首先，任何更新都会将生成的元组移动到表的末尾。其次，依赖于其他列的索引扫描的计划将以不同的顺序返回结果。即使是顺序扫描也可能不从表的开头开始。因此，如果你需要特定的顺序，你应该在 ORDER BY 子句中明确定义它。</p>
</blockquote>
<p>此处是一个处理大量行的索引扫描示例：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; EXPLAIN SELECT * FROM bookings WHERE book_ref &lt; <span class="s1">&#39;100000&#39;</span><span class="p">;</span>
</span></span><span class="line"><span class="cl">                 QUERY PLAN
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> Index Scan using bookings_pkey on bookings
</span></span><span class="line"><span class="cl">   <span class="o">(</span><span class="nv">cost</span><span class="o">=</span>0.43..4638.91 <span class="nv">rows</span><span class="o">=</span><span class="m">132999</span> <span class="nv">width</span><span class="o">=</span>21<span class="o">)</span>
</span></span><span class="line"><span class="cl">   Index Cond: <span class="o">(</span>book_ref &lt; <span class="s1">&#39;100000&#39;</span>::bpchar<span class="o">)</span>
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">3</span> rows<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>条件的选择率估算结果如下：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; SELECT round<span class="o">(</span>132999::numeric/reltuples::numeric, 4<span class="o">)</span>
</span></span><span class="line"><span class="cl">FROM pg_class WHERE <span class="nv">relname</span> <span class="o">=</span> <span class="s1">&#39;bookings&#39;</span><span class="p">;</span>
</span></span><span class="line"><span class="cl"> round
</span></span><span class="line"><span class="cl">−−−−−−−−
</span></span><span class="line"><span class="cl"> 0.0630
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">1</span> row<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>这个值接近 1/16，我们可以根据 book_ref 值的范围是从 000000 到 FFFFFF 猜到。</p>
<p>对于 B 树，I/O 成本估算中与索引相关的部分包括读取所有所需页面的成本。任何满足 B 树支持条件的索引条目均存储在有序列表的页面中，因此要读取的索引页面数量按索引大小乘以选择率进行估算。但由于这些页面物理上并不有序，因此以随机模式读取页面。</p>
<p>CPU 资源用于处理所有读取的索引条目 (处理单个条目的成本按 <span class="marginalia" data-note="0.005"><em>cpu_index_tuple_cost</em></span> 估算) 和计算每个条目的条件 (在本例中，条件包含单个运算符；其成本按 <span class="marginalia" data-note="0.0025"><em>cpu_operator_cost</em></span> 估算）。</p>
<p>表访问视为顺序读取所需的页面。在完美相关性的情况下，堆元组在磁盘上连续存储，因此页面数量按表的大小乘以选择率进行估算。</p>
<p>元组处理产生的开销会进一步增大 I/O 成本；按每条元组花费 <span class="marginalia" data-note="0.01"><em>cpu_tuple_cost</em></span> 进行估算。</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; WITH costs<span class="o">(</span>idx_cost, tbl_cost<span class="o">)</span> AS <span class="o">(</span>
</span></span><span class="line"><span class="cl">  SELECT
</span></span><span class="line"><span class="cl">    <span class="o">(</span>
</span></span><span class="line"><span class="cl">      SELECT round<span class="o">(</span>
</span></span><span class="line"><span class="cl">        current_setting<span class="o">(</span><span class="s1">&#39;random_page_cost&#39;</span><span class="o">)</span>::real * pages +
</span></span><span class="line"><span class="cl">        current_setting<span class="o">(</span><span class="s1">&#39;cpu_index_tuple_cost&#39;</span><span class="o">)</span>::real * tuples +
</span></span><span class="line"><span class="cl">        current_setting<span class="o">(</span><span class="s1">&#39;cpu_operator_cost&#39;</span><span class="o">)</span>::real * tuples
</span></span><span class="line"><span class="cl">      <span class="o">)</span>
</span></span><span class="line"><span class="cl">      FROM <span class="o">(</span>
</span></span><span class="line"><span class="cl">        SELECT relpages * 0.0630 AS pages, reltuples * 0.0630 AS tuples
</span></span><span class="line"><span class="cl">        FROM pg_class WHERE <span class="nv">relname</span> <span class="o">=</span> <span class="s1">&#39;bookings_pkey&#39;</span>
</span></span><span class="line"><span class="cl">      <span class="o">)</span> c
</span></span><span class="line"><span class="cl">    <span class="o">)</span>,
</span></span><span class="line"><span class="cl">    <span class="o">(</span>		
</span></span><span class="line"><span class="cl">      SELECT round<span class="o">(</span>
</span></span><span class="line"><span class="cl">        current_setting<span class="o">(</span><span class="s1">&#39;seq_page_cost&#39;</span><span class="o">)</span>::real * pages +
</span></span><span class="line"><span class="cl">        current_setting<span class="o">(</span><span class="s1">&#39;cpu_tuple_cost&#39;</span><span class="o">)</span>::real * tuples
</span></span><span class="line"><span class="cl">      <span class="o">)</span>
</span></span><span class="line"><span class="cl">      FROM <span class="o">(</span>
</span></span><span class="line"><span class="cl">        SELECT relpages * 0.0630 AS pages, reltuples * 0.0630 AS tuples
</span></span><span class="line"><span class="cl">        FROM pg_class WHERE <span class="nv">relname</span> <span class="o">=</span> <span class="s1">&#39;bookings&#39;</span>
</span></span><span class="line"><span class="cl">      <span class="o">)</span> c
</span></span><span class="line"><span class="cl">    <span class="o">)</span>
</span></span><span class="line"><span class="cl"><span class="o">)</span>
</span></span><span class="line"><span class="cl">SELECT idx_cost, tbl_cost, idx_cost + tbl_cost AS total
</span></span><span class="line"><span class="cl">FROM costs<span class="p">;</span>
</span></span><span class="line"><span class="cl"> idx_cost <span class="p">|</span> tbl_cost <span class="p">|</span> total
</span></span><span class="line"><span class="cl">−−−−−−−−−−+−−−−−−−−−−+−−−−−−−
</span></span><span class="line"><span class="cl"> <span class="m">2457</span>     <span class="p">|</span>     <span class="m">2177</span> <span class="p">|</span>  <span class="m">4634</span>
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">1</span> row<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>这些计算说明了成本估算背后的逻辑，因此结果与规划器提供的估算一致，即使是近似值。获得准确值将需要考虑其他细节，这些我们在这里不讨论。</p>
<h3>20.1.3 不良场景：低相关性<span class="hx-absolute -hx-mt-20" id="2013-不良场景低相关性"></span>
    <a href="#2013-%e4%b8%8d%e8%89%af%e5%9c%ba%e6%99%af%e4%bd%8e%e7%9b%b8%e5%85%b3%e6%80%a7" class="subheading-anchor" aria-label="Permalink for this section"></a></h3><p>如果相关性低，一切都变了。让我们在 book_date 列上创建一个索引，该列与这个索引的相关性几乎为零，然后观察一下获取与前一个示例中几乎相同比例行的查询。索引访问的成本太高，以至于规划器只有在其他所有可替代的方案都被明确禁止的情况下才会选择索引扫描：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; CREATE INDEX ON bookings<span class="o">(</span>book_date<span class="o">)</span><span class="p">;</span>
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; SET <span class="nv">enable_seqscan</span> <span class="o">=</span> off<span class="p">;</span>
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; SET <span class="nv">enable_bitmapscan</span> <span class="o">=</span> off<span class="p">;</span>
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; EXPLAIN SELECT * FROM bookings
</span></span><span class="line"><span class="cl">WHERE book_date &lt; <span class="s1">&#39;2016-08-23 12:00:00+03&#39;</span><span class="p">;</span>
</span></span><span class="line"><span class="cl">                             QUERY PLAN
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> Index Scan using bookings_book_date_idx on bookings
</span></span><span class="line"><span class="cl">   <span class="o">(</span><span class="nv">cost</span><span class="o">=</span>0.43..56957.48 <span class="nv">rows</span><span class="o">=</span><span class="m">132403</span> <span class="nv">width</span><span class="o">=</span>21<span class="o">)</span>
</span></span><span class="line"><span class="cl">   Index Cond: <span class="o">(</span>book_date &lt; <span class="s1">&#39;2016−08−23 12:00:00+03&#39;</span>::timestamp w...
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">3</span> rows<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>问题在于，低相关性增加了访问方法返回的下一个元组位于不同页面的可能性。因此，Index Scan 节点必须在页面之间跳转，而不是顺序读取它们；在最坏的情况下，访问的页面数量会达到获取的元组数量。</p>
<img src="20-2.png" style="width:80%; float:left" />
<div style="clear:both;"></div>
<p>然而，我们不能简单地将良好场景的计算中的 <em>seq_page_cost</em> 替换为 <em>random_page_cost</em>，relpages 替换为 reltuples。我们在计划中看到的成本远低于我们按这种方式估算的值：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql"><span class="line"><span class="cl"><span class="o">=&gt;</span><span class="w"> </span><span class="k">WITH</span><span class="w"> </span><span class="n">costs</span><span class="p">(</span><span class="n">idx_cost</span><span class="p">,</span><span class="w"> </span><span class="n">tbl_cost</span><span class="p">)</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="p">(</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">  </span><span class="k">SELECT</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    </span><span class="p">(</span><span class="w"> </span><span class="k">SELECT</span><span class="w"> </span><span class="n">round</span><span class="p">(</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">        </span><span class="n">current_setting</span><span class="p">(</span><span class="s1">&#39;random_page_cost&#39;</span><span class="p">)::</span><span class="nb">real</span><span class="w"> </span><span class="o">*</span><span class="w"> </span><span class="n">pages</span><span class="w"> </span><span class="o">+</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">        </span><span class="n">current_setting</span><span class="p">(</span><span class="s1">&#39;cpu_index_tuple_cost&#39;</span><span class="p">)::</span><span class="nb">real</span><span class="w"> </span><span class="o">*</span><span class="w"> </span><span class="n">tuples</span><span class="w"> </span><span class="o">+</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">        </span><span class="n">current_setting</span><span class="p">(</span><span class="s1">&#39;cpu_operator_cost&#39;</span><span class="p">)::</span><span class="nb">real</span><span class="w"> </span><span class="o">*</span><span class="w"> </span><span class="n">tuples</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">      </span><span class="p">)</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">      </span><span class="k">FROM</span><span class="w"> </span><span class="p">(</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">        </span><span class="k">SELECT</span><span class="w"> </span><span class="n">relpages</span><span class="w"> </span><span class="o">*</span><span class="w"> </span><span class="mi">0</span><span class="p">.</span><span class="mi">0630</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="n">pages</span><span class="p">,</span><span class="w"> </span><span class="n">reltuples</span><span class="w"> </span><span class="o">*</span><span class="w"> </span><span class="mi">0</span><span class="p">.</span><span class="mi">0630</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="n">tuples</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">        </span><span class="k">FROM</span><span class="w"> </span><span class="n">pg_class</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">relname</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="s1">&#39;bookings_pkey&#39;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">      </span><span class="p">)</span><span class="w"> </span><span class="k">c</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    </span><span class="p">),</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    </span><span class="p">(</span><span class="w"> </span><span class="k">SELECT</span><span class="w"> </span><span class="n">round</span><span class="p">(</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">        </span><span class="n">current_setting</span><span class="p">(</span><span class="s1">&#39;random_page_cost&#39;</span><span class="p">)::</span><span class="nb">real</span><span class="w"> </span><span class="o">*</span><span class="w"> </span><span class="n">tuples</span><span class="w"> </span><span class="o">+</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">        </span><span class="n">current_setting</span><span class="p">(</span><span class="s1">&#39;cpu_tuple_cost&#39;</span><span class="p">)::</span><span class="nb">real</span><span class="w"> </span><span class="o">*</span><span class="w"> </span><span class="n">tuples</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">      </span><span class="p">)</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">      </span><span class="k">FROM</span><span class="w"> </span><span class="p">(</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">        </span><span class="k">SELECT</span><span class="w"> </span><span class="n">relpages</span><span class="w"> </span><span class="o">*</span><span class="w"> </span><span class="mi">0</span><span class="p">.</span><span class="mi">0630</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="n">pages</span><span class="p">,</span><span class="w"> </span><span class="n">reltuples</span><span class="w"> </span><span class="o">*</span><span class="w"> </span><span class="mi">0</span><span class="p">.</span><span class="mi">0630</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="n">tuples</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">        </span><span class="k">FROM</span><span class="w"> </span><span class="n">pg_class</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">relname</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="s1">&#39;bookings&#39;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">      </span><span class="p">)</span><span class="w"> </span><span class="k">c</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    </span><span class="p">)</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="p">)</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">SELECT</span><span class="w"> </span><span class="n">idx_cost</span><span class="p">,</span><span class="w"> </span><span class="n">tbl_cost</span><span class="p">,</span><span class="w"> </span><span class="n">idx_cost</span><span class="w"> </span><span class="o">+</span><span class="w"> </span><span class="n">tbl_cost</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="n">total</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">costs</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"> </span><span class="n">idx_cost</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">tbl_cost</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">total</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="err">−−−−−−−−−−</span><span class="o">+</span><span class="err">−−−−−−−−−−</span><span class="o">+</span><span class="err">−−−−−−−−</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">     </span><span class="mi">2457</span><span class="w"> </span><span class="o">|</span><span class="w">   </span><span class="mi">533330</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="mi">535787</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="p">(</span><span class="mi">1</span><span class="w"> </span><span class="k">row</span><span class="p">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>原因是代价模型考虑了缓存。频繁使用的页面保存在缓冲区缓存 (和操作系统缓存) 中，因此缓存大小越大，在其中找到所需页面的机会就越大，从而避免了额外的磁盘访问操作。出于规划目的，缓存大小由 <span class="marginalia" data-note="4GB"><em>effective_cache_size</em></span> 参数定义。值越小，预估读取的页面就越多。</p>
<p>下图展示了要读取的页面数量预估值与表大小之间的依赖关系 (对于选择率为 1/2 且页面包含 10 行的情况) <sup id="fnref:5"><a href="#fn:5" class="footnote-ref" role="doc-noteref">5</a></sup>。虚线显示了最佳情况下的访问计数 (如果相关性完美，则为页面计数的一半) 和最坏情况下的访问计数 (如果相关性为零且没有缓存，则为行计数的一半) 。</p>
<img src="20-3.png" style="width:80%; float:left" />
<div style="clear:both;"></div>
<p><em>effective_cache_size</em> 值表示可用于缓存的内存总量 (包括 PostgreSQL 缓冲区缓存和操作系统缓存)。但由于该参数仅用于估算目的，并不会影响到内存分配本身，因此在更改此设置时，不必考虑实际数字。</p>
<p>如果将 <em>effective_cache_size</em> 减少到最小，计划的估算结果将接近上述无缓存情况的低端值：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; SET <span class="nv">effective_cache_size</span> <span class="o">=</span> <span class="s1">&#39;8kB&#39;</span><span class="p">;</span>
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; EXPLAIN SELECT * FROM bookings
</span></span><span class="line"><span class="cl">WHERE book_date &lt; <span class="s1">&#39;2016-08-23 12:00:00+03&#39;</span><span class="p">;</span>
</span></span><span class="line"><span class="cl">                             QUERY PLAN
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> Index Scan using bookings_book_date_idx on bookings
</span></span><span class="line"><span class="cl">   <span class="o">(</span><span class="nv">cost</span><span class="o">=</span>0.43..532745.48 <span class="nv">rows</span><span class="o">=</span><span class="m">132403</span> <span class="nv">width</span><span class="o">=</span>21<span class="o">)</span>
</span></span><span class="line"><span class="cl">   Index Cond: <span class="o">(</span>book_date &lt; <span class="s1">&#39;2016−08−23 12:00:00+03&#39;</span>::timestamp w...
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">3</span> rows<span class="o">)</span>
</span></span><span class="line"><span class="cl">               
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; RESET effective_cache_size<span class="p">;</span>
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; RESET enable_seqscan<span class="p">;</span>
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; RESET enable_bitmapscan<span class="p">;</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>规划器计算了最坏情况和最好情况场景下的表 I/O 成本，然后根据实际的相关性取一个中间值。<sup id="fnref:6"><a href="#fn:6" class="footnote-ref" role="doc-noteref">6</a></sup></p>
<p>因此，如果只需要读取一小部分行，索引扫描可能是一个不错的选择。如果堆元组与访问方法返回的 ID 顺序相关性高，那么需要读取的行数可以相对较多。然而，如果相关性低，对于选择率低的查询，索引扫描便会变得不那么吸引人。</p>
<h2>20.2 仅索引扫描<span class="hx-absolute -hx-mt-20" id="202-仅索引扫描"></span>
    <a href="#202-%e4%bb%85%e7%b4%a2%e5%bc%95%e6%89%ab%e6%8f%8f" class="subheading-anchor" aria-label="Permalink for this section"></a></h2><p>如果一个索引包含所有查询所需的堆数据，那么它被称为这个特定查询的覆盖索引。如果这样的索引可用，便可以避免额外的表访问：访问方法可以直接返回实际数据，而不是 TIDS。这种类型的索引扫描称为仅索引扫描。<sup id="fnref:7"><a href="#fn:7" class="footnote-ref" role="doc-noteref">7</a></sup> 它可以被支持 RETURNABLE 属性的访问方法使用。</p>
<p>在计划中，这个操作由 Index Only Scan <sup id="fnref:8"><a href="#fn:8" class="footnote-ref" role="doc-noteref">8</a></sup>  节点表示：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; EXPLAIN SELECT book_ref FROM bookings WHERE book_ref &lt; <span class="s1">&#39;100000&#39;</span><span class="p">;</span>
</span></span><span class="line"><span class="cl">                   QUERY PLAN
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> Index Only Scan using bookings_pkey on bookings
</span></span><span class="line"><span class="cl">   <span class="o">(</span><span class="nv">cost</span><span class="o">=</span>0.43..3791.91 <span class="nv">rows</span><span class="o">=</span><span class="m">132999</span> <span class="nv">width</span><span class="o">=</span>7<span class="o">)</span>
</span></span><span class="line"><span class="cl">   Index Cond: <span class="o">(</span>book_ref &lt; <span class="s1">&#39;100000&#39;</span>::bpchar<span class="o">)</span>
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">3</span> rows<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>顾名思义，这个节点不需要访问堆，但事实却并非如此。在 PostgreSQL 中，索引不包含元组可见性的信息，因此访问方法返回所有满足过滤条件的堆元组数据，即使当前事务看不到它们。然后由索引引擎检查它们的可见性。</p>
<p>然而，如果此方法必须访问表以检查每条元组的可见性，那么它与常规的索引扫描没有任何区别。因此，它使用了为表提供的可见性映射，在其中，vacuum 进程会标记仅包含所有元组都可见的页面 (即所有事务均可访问的那些元组，无论使用的快照如何) 。如果索引访问方法返回的 TID 属于这样的页面，则无需检查其可见性。</p>
<p>仅索引扫描的成本估算取决于堆中页面全部可见的比例。PostgreSQL 会收集这样的统计信息：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; SELECT relpages, relallvisible
</span></span><span class="line"><span class="cl">FROM pg_class WHERE <span class="nv">relname</span> <span class="o">=</span> <span class="s1">&#39;bookings&#39;</span><span class="p">;</span>
</span></span><span class="line"><span class="cl"> relpages <span class="p">|</span> relallvisible
</span></span><span class="line"><span class="cl">−−−−−−−−−−+−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl">    <span class="m">13447</span> <span class="p">|</span>         <span class="m">13446</span>
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">1</span> row<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>仅索引扫描的成本估算与常规索引扫描的成本估算不同：其与表访问相关的 I/O 成本是按照未出现在可见性映射中的页面比例来计算的。(元组处理的成本估算是相同的)</p>
<p>由于在这个特定示例中，所有页面只包含全部可见的元组，因此堆 I/O 的成本实际上排除在了成本估算之外：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; WITH costs<span class="o">(</span>idx_cost, tbl_cost<span class="o">)</span> AS <span class="o">(</span>
</span></span><span class="line"><span class="cl">  SELECT
</span></span><span class="line"><span class="cl">    <span class="o">(</span>
</span></span><span class="line"><span class="cl">      SELECT round<span class="o">(</span>
</span></span><span class="line"><span class="cl">        current_setting<span class="o">(</span><span class="s1">&#39;random_page_cost&#39;</span><span class="o">)</span>::real * pages +
</span></span><span class="line"><span class="cl">        current_setting<span class="o">(</span><span class="s1">&#39;cpu_index_tuple_cost&#39;</span><span class="o">)</span>::real * tuples +
</span></span><span class="line"><span class="cl">        current_setting<span class="o">(</span><span class="s1">&#39;cpu_operator_cost&#39;</span><span class="o">)</span>::real * tuples
</span></span><span class="line"><span class="cl">      <span class="o">)</span>
</span></span><span class="line"><span class="cl">      FROM <span class="o">(</span>
</span></span><span class="line"><span class="cl">        SELECT relpages * 0.0630 AS pages,
</span></span><span class="line"><span class="cl">              reltuples * 0.0630 AS tuples
</span></span><span class="line"><span class="cl">        FROM pg_class WHERE <span class="nv">relname</span> <span class="o">=</span> <span class="s1">&#39;bookings_pkey&#39;</span>
</span></span><span class="line"><span class="cl">      <span class="o">)</span> c
</span></span><span class="line"><span class="cl">    <span class="o">)</span> AS idx_cost,
</span></span><span class="line"><span class="cl">    <span class="o">(</span>
</span></span><span class="line"><span class="cl">      SELECT round<span class="o">(</span>
</span></span><span class="line"><span class="cl">        <span class="o">(</span><span class="m">1</span> - frac_visible<span class="o">)</span> * -- fraction of non-all-visible pages
</span></span><span class="line"><span class="cl">        current_setting<span class="o">(</span><span class="s1">&#39;seq_page_cost&#39;</span><span class="o">)</span>::real * pages +
</span></span><span class="line"><span class="cl">        current_setting<span class="o">(</span><span class="s1">&#39;cpu_tuple_cost&#39;</span><span class="o">)</span>::real * tuples
</span></span><span class="line"><span class="cl">      <span class="o">)</span>
</span></span><span class="line"><span class="cl">      FROM <span class="o">(</span>
</span></span><span class="line"><span class="cl">        SELECT relpages * 0.0630 AS pages,
</span></span><span class="line"><span class="cl">              reltuples * 0.0630 AS tuples,
</span></span><span class="line"><span class="cl">          relallvisible::real/relpages::real AS frac_visible
</span></span><span class="line"><span class="cl">        FROM pg_class WHERE <span class="nv">relname</span> <span class="o">=</span> <span class="s1">&#39;bookings&#39;</span>
</span></span><span class="line"><span class="cl">      <span class="o">)</span> c
</span></span><span class="line"><span class="cl">    <span class="o">)</span> AS tbl_cost
</span></span><span class="line"><span class="cl"><span class="o">)</span>
</span></span><span class="line"><span class="cl">SELECT idx_cost, tbl_cost, idx_cost + tbl_cost AS total
</span></span><span class="line"><span class="cl">FROM costs<span class="p">;</span>
</span></span><span class="line"><span class="cl"> idx_cost <span class="p">|</span> tbl_cost <span class="p">|</span> total
</span></span><span class="line"><span class="cl">−−−−−−−−−−+−−−−−−−−−−+−−−−−−−
</span></span><span class="line"><span class="cl">     <span class="m">2457</span> <span class="p">|</span>     <span class="m">1330</span> <span class="p">|</span> <span class="m">3787</span>
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">1</span> row<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>任何未清理的变更，如果还没有消失在数据库的视界之外，都会增加计划的预估成本 (因此，使这个计划对优化器来说变得不那么有吸引力) 。EXPLAIN ANALYZE 命令可以显示实际的堆访问计数。</p>
<p>在新创建的表中，PostgreSQL 必须检查所有元组的可见性：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; CREATE TEMP TABLE bookings_tmp
</span></span><span class="line"><span class="cl">WITH <span class="o">(</span><span class="nv">autovacuum_enabled</span> <span class="o">=</span> off<span class="o">)</span> AS
</span></span><span class="line"><span class="cl">  SELECT * FROM bookings
</span></span><span class="line"><span class="cl">  ORDER BY book_ref<span class="p">;</span>
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; ALTER TABLE bookings_tmp ADD PRIMARY KEY<span class="o">(</span>book_ref<span class="o">)</span><span class="p">;</span>
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; ANALYZE bookings_tmp<span class="p">;</span>
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; EXPLAIN <span class="o">(</span>analyze, timing off, summary off<span class="o">)</span>
</span></span><span class="line"><span class="cl">SELECT book_ref FROM bookings_tmp WHERE book_ref &lt; <span class="s1">&#39;100000&#39;</span><span class="p">;</span>
</span></span><span class="line"><span class="cl">                             QUERY PLAN
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> Index Only Scan using bookings_tmp_pkey on bookings_tmp
</span></span><span class="line"><span class="cl">   <span class="o">(</span><span class="nv">cost</span><span class="o">=</span>0.43..4638.91 <span class="nv">rows</span><span class="o">=</span><span class="m">132999</span> <span class="nv">width</span><span class="o">=</span>7<span class="o">)</span> <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span><span class="m">132109</span> l...
</span></span><span class="line"><span class="cl">   Index Cond: <span class="o">(</span>book_ref &lt; <span class="s1">&#39;100000&#39;</span>::bpchar<span class="o">)</span>
</span></span><span class="line"><span class="cl">   Heap Fetches: <span class="m">132109</span>
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">4</span> rows<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>但是一旦表被清理之后，这样的检查就会变得多余，只要所有页面保持全部可见，就不会执行这样的检查。</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; VACUUM bookings_tmp<span class="p">;</span>
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; EXPLAIN <span class="o">(</span>analyze, timing off, summary off<span class="o">)</span>
</span></span><span class="line"><span class="cl">SELECT book_ref FROM bookings_tmp WHERE book_ref &lt; <span class="s1">&#39;100000&#39;</span><span class="p">;</span>
</span></span><span class="line"><span class="cl">                             QUERY PLAN
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> Index Only Scan using bookings_tmp_pkey on bookings_tmp
</span></span><span class="line"><span class="cl">   <span class="o">(</span><span class="nv">cost</span><span class="o">=</span>0.43..3787.91 <span class="nv">rows</span><span class="o">=</span><span class="m">132999</span> <span class="nv">width</span><span class="o">=</span>7<span class="o">)</span> <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span><span class="m">132109</span> l...
</span></span><span class="line"><span class="cl">   Index Cond: <span class="o">(</span>book_ref &lt; <span class="s1">&#39;100000&#39;</span>::bpchar<span class="o">)</span>
</span></span><span class="line"><span class="cl">   Heap Fetches: <span class="m">0</span>
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">4</span> rows<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<h3>20.2.1 包含 INCLUDE 子句的索引<span class="hx-absolute -hx-mt-20" id="2021-包含-include-子句的索引"></span>
    <a href="#2021-%e5%8c%85%e5%90%ab-include-%e5%ad%90%e5%8f%a5%e7%9a%84%e7%b4%a2%e5%bc%95" class="subheading-anchor" aria-label="Permalink for this section"></a></h3><p>然而，并不总是可以使用查询所需的所有列来扩展索引：</p>
<ul>
<li>对于唯一索引，添加新列会破坏原始键列的唯一性。</li>
<li>索引访问方法可能没有为要添加的列的数据类型提供操作符类。</li>
</ul>
<p>在这种情况下，你仍然可以<span class="marginalia" data-note="v. 11">将列包含进索引中</span>，而无需让它们成为索引键的一部分。当然，基于包含列进行索引扫描是不可能的，但如果查询引用了这些列，那么索引将作为覆盖索引。</p>
<p>以下示例展示了如何用另一个包含列的索引替换自动创建的主键索引：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; CREATE UNIQUE INDEX ON bookings<span class="o">(</span>book_ref<span class="o">)</span> INCLUDE <span class="o">(</span>book_date<span class="o">)</span><span class="p">;</span>
</span></span><span class="line"><span class="cl">
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; BEGIN<span class="p">;</span>
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; ALTER TABLE bookings
</span></span><span class="line"><span class="cl">DROP CONSTRAINT bookings_pkey CASCADE<span class="p">;</span>
</span></span><span class="line"><span class="cl">NOTICE: drop cascades to constraint tickets_book_ref_fkey on table
</span></span><span class="line"><span class="cl">tickets
</span></span><span class="line"><span class="cl">ALTER <span class="nv">TABLE</span>
</span></span><span class="line"><span class="cl">
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; ALTER TABLE bookings ADD CONSTRAINT bookings_pkey PRIMARY KEY
</span></span><span class="line"><span class="cl">  USING INDEX bookings_book_ref_book_date_idx<span class="p">;</span> -- a new index
</span></span><span class="line"><span class="cl">NOTICE: ALTER TABLE / ADD CONSTRAINT USING INDEX will rename index
</span></span><span class="line"><span class="cl"><span class="s2">&#34;bookings_book_ref_book_date_idx&#34;</span> to <span class="s2">&#34;bookings_pkey&#34;</span>
</span></span><span class="line"><span class="cl">ALTER <span class="nv">TABLE</span>
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; ALTER TABLE tickets
</span></span><span class="line"><span class="cl">  ADD FOREIGN KEY <span class="o">(</span>book_ref<span class="o">)</span> REFERENCES bookings<span class="o">(</span>book_ref<span class="o">)</span><span class="p">;</span>
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; COMMIT<span class="p">;</span>
</span></span><span class="line"><span class="cl">
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; EXPLAIN SELECT book_ref, book_date
</span></span><span class="line"><span class="cl">FROM bookings WHERE book_ref &lt; <span class="s1">&#39;100000&#39;</span><span class="p">;</span>
</span></span><span class="line"><span class="cl">                             QUERY PLAN
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> Index Only Scan using bookings_pkey on bookings <span class="o">(</span><span class="nv">cost</span><span class="o">=</span>0.43..437...
</span></span><span class="line"><span class="cl">   Index Cond: <span class="o">(</span>book_ref &lt; <span class="s1">&#39;100000&#39;</span>::bpchar<span class="o">)</span>
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">2</span> rows<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<blockquote>
<p>这种索引通常被称为覆盖索引，但这种说法并不完全正确。如果一个索引的列集合覆盖了特定查询所需的所有列，那么该索引被认为是覆盖索引。无论是涉及通过 INCLUDE 子句添加的任何列，还是仅使用键列都无关紧要。此外，同一个索引对于一个查询可能是覆盖的，但对于另一个查询则可能不是。</p>
</blockquote>
<h2>20.3 位图扫描<span class="hx-absolute -hx-mt-20" id="203-位图扫描"></span>
    <a href="#203-%e4%bd%8d%e5%9b%be%e6%89%ab%e6%8f%8f" class="subheading-anchor" aria-label="Permalink for this section"></a></h2><p>索引扫描的效率是有限的：随着相关性的降低，对堆页面的访问次数增加，扫描变得随机而不是顺序的。为了克服这一限制，PostgreSQL 可以在访问表之前获取所有的 TIDS，并根据它们的页面编号按升序对它们进行排序 <sup id="fnref:9"><a href="#fn:9" class="footnote-ref" role="doc-noteref">9</a></sup>。这正是位图扫描的工作原理，位图扫描是另一种处理 TIDS 的常见方式。它可以被那些支持 BITMAP SCAN 属性的访问方法使用。</p>
<p>与常规索引扫描不同，此操作在查询计划中由两个节点表示：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; CREATE INDEX ON bookings<span class="o">(</span>total_amount<span class="o">)</span><span class="p">;</span>
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; EXPLAIN
</span></span><span class="line"><span class="cl">SELECT * FROM bookings WHERE <span class="nv">total_amount</span> <span class="o">=</span> 48500.00<span class="p">;</span>
</span></span><span class="line"><span class="cl">                             QUERY PLAN
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> Bitmap Heap Scan on bookings <span class="o">(</span><span class="nv">cost</span><span class="o">=</span>54.63..7040.42 <span class="nv">rows</span><span class="o">=</span><span class="m">2865</span> wid...
</span></span><span class="line"><span class="cl">   Recheck Cond: <span class="o">(</span><span class="nv">total_amount</span> <span class="o">=</span> 48500.00<span class="o">)</span>
</span></span><span class="line"><span class="cl">   −&gt; Bitmap Index Scan on bookings_total_amount_idx
</span></span><span class="line"><span class="cl">       <span class="o">(</span><span class="nv">cost</span><span class="o">=</span>0.00..53.92 <span class="nv">rows</span><span class="o">=</span><span class="m">2865</span> <span class="nv">width</span><span class="o">=</span>0<span class="o">)</span>
</span></span><span class="line"><span class="cl">       Index Cond: <span class="o">(</span><span class="nv">total_amount</span> <span class="o">=</span> 48500.00<span class="o">)</span>
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">5</span> rows<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>Bitmap Index Scan <sup id="fnref:10"><a href="#fn:10" class="footnote-ref" role="doc-noteref">10</a></sup> 节点从访问方法中获取所有 TIDS <sup id="fnref:11"><a href="#fn:11" class="footnote-ref" role="doc-noteref">11</a></sup>  的位图。</p>
<p>位图由单独的段组成，每个段对应一个堆页面。这些段的大小都是一样的，足以容纳所有页面元组，无论实际上有多少元组。页面元组这个数字是有限的，因为元组头非常大；标准大小的页面最多可以容纳 256 条元组，这些元组占 32 个字节 <sup id="fnref:12"><a href="#fn:12" class="footnote-ref" role="doc-noteref">12</a></sup>。</p>
<p>然后，Bitmap Heap Scan <sup id="fnref:13"><a href="#fn:13" class="footnote-ref" role="doc-noteref">13</a></sup>  逐段遍历位图，读取相应的页面，并检查所有标记为全部可见的元组。这样的话，页面根据页号以升序读取，每个页面仅被读取一次。</p>
<p>尽管如此，这个过程与顺序扫描不同，因为访问的页面很少相邻。操作系统执行的常规预取在这种情况下无济于事，因此 Bitmap Heap Scan 节点通过异步读取 <span class="marginalia" data-note="1"><em>effective_io_concurrency</em></span> 个页面实现了自己的预取 — 并且它是唯一这样做的节点。这个机制依赖于某些操作系统实现的 posix_fadvise 函数。如果你的系统支持这个函数，那么根据硬件能力在表空间级别配置 <em>effective_io_concurrency</em> 参数是有意义的。</p>
<blockquote>
<p>异步预取也被其他一些内部进程所使用：</p>
<ul>
<li>
<p><span class="marginalia" data-note="v. 13">在删除堆行时用于索引页面</span> <sup id="fnref:14"><a href="#fn:14" class="footnote-ref" role="doc-noteref">14</a></sup></p>
</li>
<li>
<p><span class="marginalia" data-note="v. 14">在分析期间用于堆页面</span> <sup id="fnref:15"><a href="#fn:15" class="footnote-ref" role="doc-noteref">15</a></sup></p>
</li>
</ul>
<p>预取深度由 <span class="marginalia" data-note="10"><em>maintenance_io_concurrency</em></span> 定义。</p>
</blockquote>
<h3>20.3.1 位图准确性<span class="hx-absolute -hx-mt-20" id="2031-位图准确性"></span>
    <a href="#2031-%e4%bd%8d%e5%9b%be%e5%87%86%e7%a1%ae%e6%80%a7" class="subheading-anchor" aria-label="Permalink for this section"></a></h3><p>包含满足查询过滤条件的元组的页面越多，位图就越大。位图建立在后端进程的本地内存中，其大小受到 <span class="marginalia" data-note="4MB"><em>work_mem</em></span> 参数限制。一旦达到允许的最大大小，一些位图段就会变得有损：有损段的每个位对应一个完整的页面，而段本身包含一系列页面。<sup id="fnref:16"><a href="#fn:16" class="footnote-ref" role="doc-noteref">16</a></sup> 因此，位图的大小以牺牲其准确性为代价变得更小。</p>
<p>EXPLAIN ANALYZE 命令显示了所构建位图的准确性：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; EXPLAIN <span class="o">(</span>analyze, costs off, timing off, summary off<span class="o">)</span>
</span></span><span class="line"><span class="cl">SELECT * FROM bookings WHERE total_amount &gt; 150000.00<span class="p">;</span>
</span></span><span class="line"><span class="cl">                             QUERY PLAN
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> Bitmap Heap Scan on bookings <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span><span class="m">242691</span> <span class="nv">loops</span><span class="o">=</span>1<span class="o">)</span>
</span></span><span class="line"><span class="cl">   Recheck Cond: <span class="o">(</span>total_amount &gt; 150000.00<span class="o">)</span>
</span></span><span class="line"><span class="cl">   Heap Blocks: <span class="nv">exact</span><span class="o">=</span><span class="m">13447</span>
</span></span><span class="line"><span class="cl">   −&gt; Bitmap Index Scan on bookings_total_amount_idx <span class="o">(</span>actual rows...
</span></span><span class="line"><span class="cl">       Index Cond: <span class="o">(</span>total_amount &gt; 150000.00<span class="o">)</span>
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">5</span> rows<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>此处，我们有足够的内存来构建一个精确的位图。</p>
<p>如果我们减少 <em>work_mem</em> 的值，一些位图段就会变得有损：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; SET <span class="nv">work_mem</span> <span class="o">=</span> <span class="s1">&#39;512kB&#39;</span><span class="p">;</span>
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; EXPLAIN <span class="o">(</span>analyze, costs off, timing off, summary off<span class="o">)</span>
</span></span><span class="line"><span class="cl">SELECT * FROM bookings WHERE total_amount &gt; 150000.00<span class="p">;</span>
</span></span><span class="line"><span class="cl">                             QUERY PLAN
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> Bitmap Heap Scan on bookings <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span><span class="m">242691</span> <span class="nv">loops</span><span class="o">=</span>1<span class="o">)</span>
</span></span><span class="line"><span class="cl">   Recheck Cond: <span class="o">(</span>total_amount &gt; 150000.00<span class="o">)</span>
</span></span><span class="line"><span class="cl">   Rows Removed by Index Recheck: <span class="m">1145721</span>
</span></span><span class="line"><span class="cl">   Heap Blocks: <span class="nv">exact</span><span class="o">=</span><span class="m">5178</span> <span class="nv">lossy</span><span class="o">=</span><span class="m">8269</span>
</span></span><span class="line"><span class="cl">   −&gt; Bitmap Index Scan on bookings_total_amount_idx <span class="o">(</span>actual rows...
</span></span><span class="line"><span class="cl">       Index Cond: <span class="o">(</span>total_amount &gt; 150000.00<span class="o">)</span>
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">6</span> rows<span class="o">)</span>
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; RESET work_mem<span class="p">;</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>当读取有损位图段所对应的堆页面时，PostgreSQL 必须重新检查页面中每条元组的过滤条件。要重新检查的条件总是在计划中显示为 Recheck Cond，即使未执行这种检查也是如此。重新检查期间过滤掉的元组数会另外显示 (作为 Rows Removed by Index Recheck)。</p>
<blockquote>
<p>如果结果集的大小太大，位图可能不适合 <em>work_mem</em> 内存块，即使所有段都是有损的。这种情况下会忽略此限制，位图会占用所需的尽可能多的空间。PostgreSQL 既不会进一步降低位图的准确性，也不会将其任何段刷到磁盘。</p>
</blockquote>
<h3>20.3.2 位图操作<span class="hx-absolute -hx-mt-20" id="2032-位图操作"></span>
    <a href="#2032-%e4%bd%8d%e5%9b%be%e6%93%8d%e4%bd%9c" class="subheading-anchor" aria-label="Permalink for this section"></a></h3><p>如果查询将过滤条件应用于多列，并且这些列上分别创建了索引，那么位图扫描可以一起使用这些索引。<sup id="fnref:17"><a href="#fn:17" class="footnote-ref" role="doc-noteref">17</a></sup> 所有这些索引都即时构建了自己的位图；然后使用逻辑与 (如果表达式通过 AND 连接) 或逻辑或 (如果表达式通过 OR 连接) 将位图按位合并。例如：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; EXPLAIN <span class="o">(</span>costs off<span class="o">)</span>
</span></span><span class="line"><span class="cl">SELECT * FROM bookings
</span></span><span class="line"><span class="cl">WHERE book_date &lt; <span class="s1">&#39;2016-08-28&#39;</span>
</span></span><span class="line"><span class="cl">	AND total_amount &gt; 250000<span class="p">;</span>
</span></span><span class="line"><span class="cl">                             QUERY PLAN
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> Bitmap Heap Scan on bookings
</span></span><span class="line"><span class="cl">   Recheck Cond: <span class="o">((</span>total_amount &gt; <span class="s1">&#39;250000&#39;</span>::numeric<span class="o">)</span> AND <span class="o">(</span>book_da...
</span></span><span class="line"><span class="cl">   −&gt; BitmapAnd
</span></span><span class="line"><span class="cl">       −&gt; Bitmap Index Scan on bookings_total_amount_idx
</span></span><span class="line"><span class="cl">           Index Cond: <span class="o">(</span>total_amount &gt; <span class="s1">&#39;250000&#39;</span>::numeric<span class="o">)</span>
</span></span><span class="line"><span class="cl">       −&gt; Bitmap Index Scan on bookings_book_date_idx
</span></span><span class="line"><span class="cl">           Index Cond: <span class="o">(</span>book_date &lt; <span class="s1">&#39;2016−08−28 00:00:00+03&#39;</span>::tim...
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">7</span> rows<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>此处 BitmapAnd 节点使用按位与操作合并两个位图。</p>
<p>当两个位图合并为一个时，<sup id="fnref:18"><a href="#fn:18" class="footnote-ref" role="doc-noteref">18</a></sup> 精确的段在合并时仍然保持精确 (如果新的位图适合 <em>work_mem</em> 内存块)，但如果一组位图中有任何段是有损的，那么生成的段也将是有损的。</p>
<h3>20.3.3 成本估算<span class="hx-absolute -hx-mt-20" id="2033-成本估算"></span>
    <a href="#2033-%e6%88%90%e6%9c%ac%e4%bc%b0%e7%ae%97" class="subheading-anchor" aria-label="Permalink for this section"></a></h3><p>让我们观察一下使用位图扫描的查询：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; EXPLAIN
</span></span><span class="line"><span class="cl">SELECT * FROM bookings WHERE <span class="nv">total_amount</span> <span class="o">=</span> 28000.00<span class="p">;</span>
</span></span><span class="line"><span class="cl">                             QUERY PLAN
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> Bitmap Heap Scan on bookings <span class="o">(</span><span class="nv">cost</span><span class="o">=</span>599.48..14444.96 <span class="nv">rows</span><span class="o">=</span><span class="m">31878</span> ...
</span></span><span class="line"><span class="cl">   Recheck Cond: <span class="o">(</span><span class="nv">total_amount</span> <span class="o">=</span> 28000.00<span class="o">)</span>
</span></span><span class="line"><span class="cl">   −&gt; Bitmap Index Scan on bookings_total_amount_idx
</span></span><span class="line"><span class="cl">       <span class="o">(</span><span class="nv">cost</span><span class="o">=</span>0.00..591.51 <span class="nv">rows</span><span class="o">=</span><span class="m">31878</span> <span class="nv">width</span><span class="o">=</span>0<span class="o">)</span>
</span></span><span class="line"><span class="cl">       Index Cond: <span class="o">(</span><span class="nv">total_amount</span> <span class="o">=</span> 28000.00<span class="o">)</span>
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">5</span> rows<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>规划器使用的条件的近似选择率等于</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; SELECT round<span class="o">(</span>31878::numeric/reltuples::numeric, 4<span class="o">)</span>
</span></span><span class="line"><span class="cl">FROM pg_class WHERE <span class="nv">relname</span> <span class="o">=</span> <span class="s1">&#39;bookings&#39;</span><span class="p">;</span>
</span></span><span class="line"><span class="cl"> round
</span></span><span class="line"><span class="cl">−−−−−−−−
</span></span><span class="line"><span class="cl"> 0.0151
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">1</span> row<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>Bitmap Index Scan 节点的总成本以与不考虑堆访问的常规索引扫描相同的方式进行估算：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; SELECT round<span class="o">(</span>
</span></span><span class="line"><span class="cl">  current_setting<span class="o">(</span><span class="s1">&#39;random_page_cost&#39;</span><span class="o">)</span>::real * pages +
</span></span><span class="line"><span class="cl">  current_setting<span class="o">(</span><span class="s1">&#39;cpu_index_tuple_cost&#39;</span><span class="o">)</span>::real * tuples +
</span></span><span class="line"><span class="cl">  current_setting<span class="o">(</span><span class="s1">&#39;cpu_operator_cost&#39;</span><span class="o">)</span>::real * tuples
</span></span><span class="line"><span class="cl"><span class="o">)</span>
</span></span><span class="line"><span class="cl">FROM <span class="o">(</span>
</span></span><span class="line"><span class="cl">  SELECT relpages * 0.0151 AS pages, reltuples * 0.0151 AS tuples
</span></span><span class="line"><span class="cl">  FROM pg_class WHERE <span class="nv">relname</span> <span class="o">=</span> <span class="s1">&#39;bookings_total_amount_idx&#39;</span>
</span></span><span class="line"><span class="cl"><span class="o">)</span> c<span class="p">;</span>
</span></span><span class="line"><span class="cl"> round
</span></span><span class="line"><span class="cl">−−−−−−−
</span></span><span class="line"><span class="cl">   <span class="m">589</span>
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">1</span> row<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>Bitmap Heap Scan 节点的 I/O 成本估算不同于常规索引扫描完美相关性的情况。位图允许根据页号升序读取堆页，而不需要返回到同一页面，但满足过滤条件的元组不再相继出现。PostgreSQL 可能会访问更多页面，而不是读取一个非常紧凑的严格顺序的页面范围。</p>
<img src="20-4.png" style="width:80%; float:left" />
<div style="clear:both;"></div>
<p>要读取的页面数通过以下公式估算：<sup id="fnref:19"><a href="#fn:19" class="footnote-ref" role="doc-noteref">19</a></sup>
$$
\min \left( \frac{2 \cdot \text{relpages} \cdot \text{reltuples} \cdot \text{sel}}{2 \cdot \text{relpages} + \text{reltuples} \cdot \text{sel}}, \text{relpages} \right)
$$
读取单个页面的估算成本介于 <em>seq_page_cost</em> 和 <em>random_page_cost</em> 之间，取决于获取的页面比例与表中页面总数的比率：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; WITH t AS <span class="o">(</span>
</span></span><span class="line"><span class="cl">  SELECT relpages,
</span></span><span class="line"><span class="cl">    least<span class="o">(</span>
</span></span><span class="line"><span class="cl">      <span class="o">(</span><span class="m">2</span> * relpages * reltuples * 0.0151<span class="o">)</span> /
</span></span><span class="line"><span class="cl">      <span class="o">(</span><span class="m">2</span> * relpages + reltuples * 0.0151<span class="o">)</span>,
</span></span><span class="line"><span class="cl">      relpages
</span></span><span class="line"><span class="cl">    <span class="o">)</span> AS pages_fetched,
</span></span><span class="line"><span class="cl">    round<span class="o">(</span>reltuples * 0.0151<span class="o">)</span> AS tuples_fetched,
</span></span><span class="line"><span class="cl">    current_setting<span class="o">(</span><span class="s1">&#39;random_page_cost&#39;</span><span class="o">)</span>::real AS rnd_cost,
</span></span><span class="line"><span class="cl">    current_setting<span class="o">(</span><span class="s1">&#39;seq_page_cost&#39;</span><span class="o">)</span>::real AS seq_cost
</span></span><span class="line"><span class="cl">  FROM pg_class WHERE <span class="nv">relname</span> <span class="o">=</span> <span class="s1">&#39;bookings&#39;</span>
</span></span><span class="line"><span class="cl"><span class="o">)</span>
</span></span><span class="line"><span class="cl">SELECT pages_fetched,
</span></span><span class="line"><span class="cl">  rnd_cost - <span class="o">(</span>rnd_cost - seq_cost<span class="o">)</span> *
</span></span><span class="line"><span class="cl">  sqrt<span class="o">(</span>pages_fetched / relpages<span class="o">)</span> AS cost_per_page,
</span></span><span class="line"><span class="cl">  tuples_fetched
</span></span><span class="line"><span class="cl">FROM t<span class="p">;</span>
</span></span><span class="line"><span class="cl"> pages_fetched <span class="p">|</span> cost_per_page <span class="p">|</span> tuples_fetched
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−+−−−−−−−−−−−−−−−+−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl">         <span class="m">13447</span> <span class="p">|</span>             <span class="m">1</span> <span class="p">|</span>          <span class="m">31878</span>
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">1</span> row<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>与往常一样，I/O 估算值会因处理每条获取元组的成本而增加。如果使用精确位图，元组数估算为表中元组总数乘以过滤条件的选择率。但是，如果任何位图段是有损的，PostgreSQL 必须访问相应的页面以重新检查其中所有元组。</p>
<img src="20-5.png" style="width:80%; float:left" />
<div style="clear:both;"></div>
<p>因此，估算<span class="marginalia" data-note="v. 11">考虑了有损位图段的预期比例</span> (可以基于所选行的总数和 <em>work_mem</em> 定义的位图大小限制来计算)。<sup id="fnref:20"><a href="#fn:20" class="footnote-ref" role="doc-noteref">20</a></sup></p>
<p>重新检查过滤条件的总成本也增加了估算值 (无论位图精度如何)。</p>
<p>Bitmap Heap Scan 节点的启动成本估算值基于 Bitmap Index Scan 节点的总成本，该成本还包括了位图处理的成本。</p>
<img src="20-6.png" style="width:80%; float:left" />
<div style="clear:both;"></div>
<p>此处位图是准确的，成本估算大致如下：<sup id="fnref:21"><a href="#fn:21" class="footnote-ref" role="doc-noteref">21</a></sup></p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; WITH t AS <span class="o">(</span>
</span></span><span class="line"><span class="cl">  SELECT <span class="m">1</span> AS cost_per_page,
</span></span><span class="line"><span class="cl">         <span class="m">13447</span> AS pages_fetched,
</span></span><span class="line"><span class="cl">         <span class="m">31878</span> AS tuples_fetched
</span></span><span class="line"><span class="cl"><span class="o">)</span>,
</span></span><span class="line"><span class="cl">costs<span class="o">(</span>startup_cost, run_cost<span class="o">)</span> AS <span class="o">(</span>
</span></span><span class="line"><span class="cl">  SELECT
</span></span><span class="line"><span class="cl">    <span class="o">(</span> SELECT round<span class="o">(</span>
</span></span><span class="line"><span class="cl">        <span class="m">589</span> /* cost estimation <span class="k">for</span> the child node */ +
</span></span><span class="line"><span class="cl">        0.1 * current_setting<span class="o">(</span><span class="s1">&#39;cpu_operator_cost&#39;</span><span class="o">)</span>::real *
</span></span><span class="line"><span class="cl">        reltuples * 0.0151
</span></span><span class="line"><span class="cl">      <span class="o">)</span>
</span></span><span class="line"><span class="cl">      FROM pg_class WHERE <span class="nv">relname</span> <span class="o">=</span> <span class="s1">&#39;bookings_total_amount_idx&#39;</span>
</span></span><span class="line"><span class="cl">    <span class="o">)</span>,
</span></span><span class="line"><span class="cl">    <span class="o">(</span> SELECT round<span class="o">(</span>
</span></span><span class="line"><span class="cl">        cost_per_page * pages_fetched +
</span></span><span class="line"><span class="cl">        current_setting<span class="o">(</span><span class="s1">&#39;cpu_tuple_cost&#39;</span><span class="o">)</span>::real * tuples_fetched +
</span></span><span class="line"><span class="cl">        current_setting<span class="o">(</span><span class="s1">&#39;cpu_operator_cost&#39;</span><span class="o">)</span>::real * tuples_fetched
</span></span><span class="line"><span class="cl">      <span class="o">)</span>
</span></span><span class="line"><span class="cl">      FROM t
</span></span><span class="line"><span class="cl">    <span class="o">)</span>
</span></span><span class="line"><span class="cl"><span class="o">)</span>
</span></span><span class="line"><span class="cl">SELECT startup_cost, run_cost,
</span></span><span class="line"><span class="cl">  startup_cost + run_cost AS total_cost
</span></span><span class="line"><span class="cl">FROM costs<span class="p">;</span>
</span></span><span class="line"><span class="cl"> startup_cost <span class="p">|</span> run_cost <span class="p">|</span> total_cost
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−+−−−−−−−−−−+−−−−−−−−−−−−
</span></span><span class="line"><span class="cl">          <span class="m">597</span> <span class="p">|</span>    <span class="m">13845</span> <span class="p">|</span>      <span class="m">14442</span>
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">1</span> row<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>如果查询计划合并了多个位图，单独索引扫描的成本总和会增加一个 (较小) 合并它们的成本。<sup id="fnref:22"><a href="#fn:22" class="footnote-ref" role="doc-noteref">22</a></sup></p>
<h2>20.4 并行索引扫描<span class="hx-absolute -hx-mt-20" id="204-并行索引扫描"></span>
    <a href="#204-%e5%b9%b6%e8%a1%8c%e7%b4%a2%e5%bc%95%e6%89%ab%e6%8f%8f" class="subheading-anchor" aria-label="Permalink for this section"></a></h2><p>所有索引扫描的方式 — 常规索引扫描、仅索引扫描和位图扫描都有<span class="marginalia" data-note="v. 9.6">其各自的并行特点</span>。</p>
<p>并行执行的成本以与顺序执行相同的方式进行估算，但是 (就像并行顺序扫描的情况一样) CPU 资源在所有并行进程之间分配，从而降低了总成本。成本的 I/O 组成部分没有分摊，因为进程被同步以顺序执行页面访问。</p>
<p>现在让我在不分解成本估算的情况下展示几个并行计划的示例。</p>
<p>并行索引扫描：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; EXPLAIN SELECT sum<span class="o">(</span>total_amount<span class="o">)</span>
</span></span><span class="line"><span class="cl">FROM bookings WHERE book_ref &lt; <span class="s1">&#39;400000&#39;</span><span class="p">;</span>
</span></span><span class="line"><span class="cl">                             QUERY PLAN
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> Finalize Aggregate <span class="o">(</span><span class="nv">cost</span><span class="o">=</span>19192.81..19192.82 <span class="nv">rows</span><span class="o">=</span><span class="m">1</span> <span class="nv">width</span><span class="o">=</span>32<span class="o">)</span>
</span></span><span class="line"><span class="cl">   −&gt; Gather <span class="o">(</span><span class="nv">cost</span><span class="o">=</span>19192.59..19192.80 <span class="nv">rows</span><span class="o">=</span><span class="m">2</span> <span class="nv">width</span><span class="o">=</span>32<span class="o">)</span>
</span></span><span class="line"><span class="cl">       Workers Planned: <span class="m">2</span>
</span></span><span class="line"><span class="cl">       −&gt; Partial Aggregate <span class="o">(</span><span class="nv">cost</span><span class="o">=</span>18192.59..18192.60 <span class="nv">rows</span><span class="o">=</span><span class="m">1</span> widt...
</span></span><span class="line"><span class="cl">           −&gt; Parallel Index Scan using bookings_pkey on bookings
</span></span><span class="line"><span class="cl">               <span class="o">(</span><span class="nv">cost</span><span class="o">=</span>0.43..17642.82 <span class="nv">rows</span><span class="o">=</span><span class="m">219907</span> <span class="nv">width</span><span class="o">=</span>6<span class="o">)</span>
</span></span><span class="line"><span class="cl">               Index Cond: <span class="o">(</span>book_ref &lt; <span class="s1">&#39;400000&#39;</span>::bpchar<span class="o">)</span>
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">7</span> rows<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>当正在执行 B 树并行扫描时，当前索引页的 ID 保存在服务器的共享内存中。初始值由启动扫描的进程设置：它从根遍历树，到第一个合适的叶子页面并保存其 ID。工作进程根据需要访问后续索引页面，替换保存的 ID。获取页面后，工作进程遍历其所有合适的条目并读取相应的堆元组。当工作进程读取了满足查询过滤条件的整个值范围时，扫描完成。</p>
<p>并行仅索引扫描：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; EXPLAIN SELECT sum<span class="o">(</span>total_amount<span class="o">)</span>
</span></span><span class="line"><span class="cl">FROM bookings WHERE total_amount &lt; 50000.00<span class="p">;</span>
</span></span><span class="line"><span class="cl">                             QUERY PLAN
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> Finalize Aggregate <span class="o">(</span><span class="nv">cost</span><span class="o">=</span>23370.60..23370.61 <span class="nv">rows</span><span class="o">=</span><span class="m">1</span> <span class="nv">width</span><span class="o">=</span>32<span class="o">)</span>
</span></span><span class="line"><span class="cl">   −&gt; Gather <span class="o">(</span><span class="nv">cost</span><span class="o">=</span>23370.38..23370.59 <span class="nv">rows</span><span class="o">=</span><span class="m">2</span> <span class="nv">width</span><span class="o">=</span>32<span class="o">)</span>
</span></span><span class="line"><span class="cl">       Workers Planned: <span class="m">2</span>
</span></span><span class="line"><span class="cl">       −&gt; Partial Aggregate <span class="o">(</span><span class="nv">cost</span><span class="o">=</span>22370.38..22370.39 <span class="nv">rows</span><span class="o">=</span><span class="m">1</span> widt...
</span></span><span class="line"><span class="cl">           −&gt; Parallel Index Only Scan using bookings_total_amoun...
</span></span><span class="line"><span class="cl">               <span class="o">(</span><span class="nv">cost</span><span class="o">=</span>0.43..21387.27 <span class="nv">rows</span><span class="o">=</span><span class="m">393244</span> <span class="nv">width</span><span class="o">=</span>6<span class="o">)</span>
</span></span><span class="line"><span class="cl">               Index Cond: <span class="o">(</span>total_amount &lt; 50000.00<span class="o">)</span>
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">7</span> rows<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>并行仅索引扫描跳过了页面全部可见的堆访问；这是它与并行索引扫描的唯一区别。</p>
<p>并行位图扫描：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; EXPLAIN SELECT sum<span class="o">(</span>total_amount<span class="o">)</span>
</span></span><span class="line"><span class="cl">FROM bookings WHERE book_date &lt; <span class="s1">&#39;2016-10-01&#39;</span><span class="p">;</span>
</span></span><span class="line"><span class="cl">                             QUERY PLAN
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> Finalize Aggregate <span class="o">(</span><span class="nv">cost</span><span class="o">=</span>21492.21..21492.22 <span class="nv">rows</span><span class="o">=</span><span class="m">1</span> <span class="nv">width</span><span class="o">=</span>32<span class="o">)</span>
</span></span><span class="line"><span class="cl">   −&gt; Gather <span class="o">(</span><span class="nv">cost</span><span class="o">=</span>21491.99..21492.20 <span class="nv">rows</span><span class="o">=</span><span class="m">2</span> <span class="nv">width</span><span class="o">=</span>32<span class="o">)</span>
</span></span><span class="line"><span class="cl">       Workers Planned: <span class="m">2</span>
</span></span><span class="line"><span class="cl">       −&gt; Partial Aggregate <span class="o">(</span><span class="nv">cost</span><span class="o">=</span>20491.99..20492.00 <span class="nv">rows</span><span class="o">=</span><span class="m">1</span> widt...
</span></span><span class="line"><span class="cl">           −&gt; Parallel Bitmap Heap Scan on bookings
</span></span><span class="line"><span class="cl">               <span class="o">(</span><span class="nv">cost</span><span class="o">=</span>4891.17..20133.01 <span class="nv">rows</span><span class="o">=</span><span class="m">143588</span> <span class="nv">width</span><span class="o">=</span>6<span class="o">)</span>
</span></span><span class="line"><span class="cl">               Recheck Cond: <span class="o">(</span>book_date &lt; <span class="s1">&#39;2016−10−01 00:00:00+03...
</span></span></span><span class="line"><span class="cl"><span class="s1">               −&gt; Bitmap Index Scan on bookings_book_date_idx
</span></span></span><span class="line"><span class="cl"><span class="s1">                   (cost=0.00..4805.01 rows=344611 width=0)
</span></span></span><span class="line"><span class="cl"><span class="s1">                   Index Cond: (book_date &lt; &#39;</span>2016−10−01 00:00:00+...</span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>位图扫描意味着位图总是由单个领导者进程顺序构建；因此，Bitmap Index Scan 节点的名称不包含 Parallel 一词。当位图准备就绪后，Parallel Bitmap Heap Scan 节点开始并行堆扫描。工作进程访问后续的堆页面并且并发处理它们。</p>
<h2>20.5 各种访问方法的比较<span class="hx-absolute -hx-mt-20" id="205-各种访问方法的比较"></span>
    <a href="#205-%e5%90%84%e7%a7%8d%e8%ae%bf%e9%97%ae%e6%96%b9%e6%b3%95%e7%9a%84%e6%af%94%e8%be%83" class="subheading-anchor" aria-label="Permalink for this section"></a></h2><p>下图显示了过滤条件的选择率如何影响访问方法的成本：</p>
<img src="20-7.png" style="width:80%; float:left" />
<div style="clear:both;"></div>
<p>这是一个定性图；实际数字当然取决于特定的表和服务器配置。</p>
<p>顺序扫描不依赖于选择率，从选定行的某个比例开始，它通常比其他方法更有效。</p>
<p>索引扫描的成本受到元组的物理顺序与访问方法返回的 ID 顺序之间的相关性的影响。如果相关性完美，即使所选行的比例很高，索引扫描也可以非常高效。然而，对于低相关性 (这更常见) ，它的成本很快甚至可能超过顺序扫描。尽管如此，在使用索引获取单行时 (通常是唯一索引)，索引扫描仍然是绝对领先者。</p>
<p>如果适用，仅索引扫描可以展现出极佳的性能，甚至在选择所有行时也能胜过顺序扫描。但是，其性能高度依赖于可见性映射，在最糟糕的情况下，仅索引扫描会降级为常规的索引扫描。</p>
<p>位图扫描的成本受可用内存大小的影响，但这种影响远小于索引扫描的成本对于相关性的依赖。如果相关性低，位图扫描的成本会更低。</p>
<p>每种访问方法都有其适用的使用场景；没有一种方法总是优于其他方法。规划器必须进行大量计算，以评估每种特定情况下每种方法的效率。显然，评估的准确性在很大程度上取决于所收集的统计信息的准确性。</p>
<div class="footnotes" role="doc-endnotes">
<hr>
<ol>
<li id="fn:1">
<p>backend/executor/nodeIndexscan.c&#160;<a href="#fnref:1" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:2">
<p>backend/access/index/indexam.c, index_getnext_tid function&#160;<a href="#fnref:2" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:3">
<p>backend/executor/nodeTidscan.c&#160;<a href="#fnref:3" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:4">
<p>backend/utils/adt/selfuncs.c, btcostestimate function<br>postgresql.org/docs/14/index-cost-estimation.html&#160;<a href="#fnref:4" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:5">
<p>backend/optimizer/path/costsize.c, index_pages_fetched function&#160;<a href="#fnref:5" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:6">
<p>backend/optimizer/path/costsize.c, cost_index function&#160;<a href="#fnref:6" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:7">
<p>postgresql.org/docs/14/indexes-index-only-scans.html&#160;<a href="#fnref:7" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:8">
<p>backend/executor/nodeIndexonlyscan.c&#160;<a href="#fnref:8" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:9">
<p>backend/access/index/indexam.c, index_getbitmap function&#160;<a href="#fnref:9" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:10">
<p>backend/executor/nodeBitmapIndexscan.c&#160;<a href="#fnref:10" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:11">
<p>backend/access/index/indexam.c, index_getbitmap function&#160;<a href="#fnref:11" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:12">
<p>backend/nodes/tidbitmap.c&#160;<a href="#fnref:12" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:13">
<p>backend/executor/nodeBitmapHeapscan.c&#160;<a href="#fnref:13" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:14">
<p>backend/access/heap/heapam.c, index_delete_prefetch_buffer function&#160;<a href="#fnref:14" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:15">
<p>backend/commands/analyze.c, acquire_sample_rows function&#160;<a href="#fnref:15" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:16">
<p>backend/nodes/tidbitmap.c, tbm_lossify function&#160;<a href="#fnref:16" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:17">
<p>postgresql.org/docs/14/indexes-ordering.html&#160;<a href="#fnref:17" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:18">
<p>backend/nodes/tidbitmap.c, tbm_union &amp; tbm_intersect functions&#160;<a href="#fnref:18" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:19">
<p>backend/optimizer/path/costsize.c, compute_bitmap_pages function&#160;<a href="#fnref:19" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:20">
<p>backend/optimizer/path/costsize.c, compute_bitmap_pages function&#160;<a href="#fnref:20" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:21">
<p>backend/optimizer/path/costsize.c, cost_bitmap_heap_scan function&#160;<a href="#fnref:21" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:22">
<p>backend/optimizer/path/costsize.c, cost_bitmap_and_node &amp; cost_bitmap_or_node functions&#160;<a href="#fnref:22" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
</ol>
</div>

        </div>
        <div class="hx-mt-16"></div>
        
        
      </main>
    </article>
  </div>

      <footer class="hextra-footer hx-bg-gray-100 hx-pb-[env(safe-area-inset-bottom)] dark:hx-bg-neutral-900 print:hx-bg-transparent"><div
    class="hx-max-w-screen-xl hx-mx-auto hx-flex hx-justify-center hx-py-12 hx-pl-[max(env(safe-area-inset-left),1.5rem)] hx-pr-[max(env(safe-area-inset-right),1.5rem)] hx-text-gray-600 dark:hx-text-gray-400 md:hx-justify-start"
  >
    <div class="hx-flex hx-w-full hx-flex-col hx-items-center sm:hx-items-start"><div class="hx-mt-6 hx-text-xs">本中文译文版权归熊灿灿所有。英文原文版权归 Postgres Professional 所有。本译文根据授权翻译与发布。</div>
    </div>
  </div>
</footer>
    
    <script defer src="/js/main.js" integrity=""></script>


<script defer src="/lib/flexsearch/flexsearch.bundle.min.0425860527cc9968f9f049421c7a56b39327d475e2e3a8f550416be3a9134327.js" integrity="sha256-BCWGBSfMmWj58ElCHHpWs5Mn1HXi46j1UEFr46kTQyc="></script>
    <script defer src="/en.search.js" integrity=""></script>
<link type="text/css" rel="stylesheet" href="/lib/katex/katex.min.7e5db7914b97e596a36c1abb67ccc7f174f8bb71d38c9a88c55b262ed1737f97.css" integrity="sha256-fl23kUuX5ZajbBq7Z8zH8XT4u3HTjJqIxVsmLtFzf5c=" />
  <script defer src="/lib/katex/katex.min.9f45307c5794ed247a0d095f3a62e52ef2215a67b2327203a7fd919959ae79d1.js" integrity="sha256-n0UwfFeU7SR6DQlfOmLlLvIhWmeyMnIDp/2RmVmuedE="></script>
  <script defer src="/lib/katex/auto-render.min.7b57d427ac6270677daf8d8380ded2cc73336f9149a167b8e1fe0d6ef66604ae.js" integrity="sha256-e1fUJ6xicGd9r42DgN7SzHMzb5FJoWe44f4NbvZmBK4="></script>
  <script defer src="/lib/katex/mhchem.min.f0ca03df194b8c3d6017ff455db6a0ef98857905663fa311a6cded788b15340b.js" integrity="sha256-8MoD3xlLjD1gF/9FXbag75iFeQVmP6MRps3teIsVNAs="></script>
  <script>
    
    
    document.addEventListener("DOMContentLoaded", function () {
      renderMathInElement(document.body, {
        delimiters: [
          { left: "$$", right: "$$", display: true },
          { left: "$", right: "$", display: false },
          { left: "\\(", right: "\\)", display: false },
          { left: "\\begin{equation}", right: "\\end{equation}", display: true },
          {left: "\\begin{align}", right: "\\end{align}", display: true},
          {left: "\\begin{alignat}", right: "\\end{alignat}", display: true},
          {left: "\\begin{gather}", right: "\\end{gather}", display: true},
          {left: "\\begin{CD}", right: "\\end{CD}", display: true},
          { left: "\\[", right: "\\]", display: true },
        ],
        throwOnError: false,
      });
    });
  </script>


  </body>
</html>
